Absolute and Relative Dates
You can apply a time element to your report by dragging a date or set of dates from the Reporting pane to selected columns in the Excel worksheet. You can refine these time elements and automate periodic reporting by specifying whether the time elements should be absolute or relative to your specified report date, which is the date your report is based on.
An absolute date is always the same, regardless of any changes to your report date. A relative date is updated when you change your report date.
For example, suppose you design a report that has a report date of October 1, 2014, and then you apply time elements to show that the account data is for August and September 2014. If these are absolute dates, the time elements will always be displayed as August 2014 and September 2014, even if you change your report date to December 1, 2014.
If you specify that relative dates be used, then the time elements will reflect -2 Months and -1 Month. In this case, if you change your report date to December 1, 2014, the time elements will dynamically pull in data from October 2014 and November 2014.
You can set up a report you can easily reuse across succeeding time periods. If you generate a particular type of report every month or every quarter, for example, set it up with relative dates. All dates are relative from the report date set for the workbook. When you want to update the workbook for the new period, simply change the report date and then refresh the report. OfficeConnect pulls the data for the specified dates relative to the new report date.
Note: With relative dates, you can define a label in the worksheet to show the fiscal period, for example, Q3-FY14. See Adding an Adaptive Insights Label to a Report for more information.
You might need absolute dates for certain reports, relative dates for other reports, and a mixture of absolute and relative dates for still others. In this section, you’ll learn how to change the date type for a new or existing report, for an existing time element, and for new ones.
Specify the Report Date and Default Date Type
When you create a new workbook, either by starting up OfficeConnect or by clicking File > New, the New Book dialog box appears and prompts you for the report date:
The default indicates that time elements are relative to the report date. If you want most time elements to be absolute, clear Make new Calendar elements relative to Report Date. You can make exceptions to the default. You can have a mix of absolute and relative dates in your report.
To change the report date or date type in an existing workbook:
From the OfficeConnect tab > Book group, click Book Properties.
The Book Properties dialog box appears and the Summary tab is active.
In the Report Date box, enter the report date you want.
By default, this is the date the report is created. The report date is particularly important if you’re using relative dates. Be sure that your report date is set before changing any time elements from absolute to relative.
Select or clear Make new Calendar elements relative to Report Date.
The default indicates that time elements should be relative to the report date. Make your selection based on which date type you expect to use the most in your report.
The date type changes for any new time elements you apply to the reports in this workbook. Any existing time elements remain unchanged. If you want to change existing time elements, see Apply Absolute and Relative Dates in this article for more information.
You can only have one report date in a workbook, even across multiple worksheets in the workbook.
Apply Absolute and Relative Dates
You can apply both absolute and relative time elements in the same report. Your date type setting in the Book Properties dialog box applies to any new time elements added to the report. Any existing time elements applied to the report remain unchanged.
You can set the absolute date type and apply to the time elements as required. You can change the date type to relative and apply the time elements must be relative to the report date.
If you want to change the date type for an individual time element, delete the existing time element from the report and reapply it to adopt the date type set in the Book Properties dialog box.
Important: Set your report date before changing any time elements from absolute to relative.
To change the date type of an existing time element:
- Select the column containing the existing time element.
- Click the Review tab from the Reporting pane.
The Review pane shows the metadata for the selected column, including the time element.
- Right-click the metadata line for the time element.
Example: right-click Calendar = -6 Quarters [Calendar Year] and toggle between Switch to Relative or Switch to Absolute.
- Click Refresh from the OfficeConnect tab > Design group to see the results of your calendar change.