Example: Using the PeriodOffset and Parent Functions to Return Rolling Quarters
The PeriodOffset function allows returning relative Period dimension members across both the Period and Year dimensions. This function can be used to create a rolling report across months and years.
The PeriodOffset function returns only level 0 periods, however by combining it with the Parent function, you can return quarters in a rolling report. For example, display the prior 4 quarters relative to the current quarter.
The following example utilizes the Narrative Reporting Sample Application. Note the following report design:
The Account dimension, with Operating Expenses selected, is in the grid rows. The Years and Fiscal Calendar (Period) dimensions are in the columns. The Years dimension has CurrentPOV selected for all five columns. The Fiscal Calendar dimension utilizes a combination of the CurrentPOV, Parent, and PeriodOffset members, detailed below. Note that the Fiscal Calendar POV member selection definitions are just the four quarter-end months (March, June, September, and December).
Table 9-2 The PeriodOffset and Parent functions to return rolling quarters
| Column | Function | Description |
|---|---|---|
| A | Parent ( PeriodOffset of CurrentPOV-12 ) | Takes the CurrentPOV month selection, returns the month for 12 months prior, spanning across Years, then returns the parent quarter member for the month. |
| B | Parent ( PeriodOffset of CurrentPOV-9 ) | Takes the CurrentPOV month selection, returns the month for 9 months prior, spanning across Years, then returns the parent quarter member for the month. |
| C | Parent ( PeriodOffset of CurrentPOV-6 ) | Takes the CurrentPOV month selection, returns the month for 6 months prior, spanning across Years, then returns the parent quarter member for the month. |
| D | Parent ( PeriodOffset of CurrentPOV-3 ) | Takes the CurrentPOV month selection, returns the month for 3 months prior, spanning across Years, then returns the parent quarter member for the month. |
| E | Parent of Current POV | Returns the parent quarter of the CurrentPOV month selection |
Note the following results with Years = 2023 and Fiscal Calendar = June. The last shaded column returns Quarter2 of 2023, reflecting the quarter for the currently select month of June. The prior 4 columns return the prior four quarters across years, based on the June selection:
The following example shows the results for Fiscal Calendar = March, reflecting the current quarter in the last shaded column and the prior 4 columns return the prior four quarters across years, based on the March selection.
