Out of Box Dynamic Time Series
You can use
Dynamic Time Series (DTS)
members to create reports that show period-to-date data, such as quarter-to-date expenses. DTS members are used with members of the Period dimension. For example, to calculate quarter-to-date values,
you can enable the Q-T-D member and associate it with generation number 2. You can then use the Q-T-D DTS period to calculate monthly values up to the current month in the quarter.
There are 8 predefined Dynamic Time Series members:
1. Y-T-D: Year-to-date
2. H-T-D: History-to-date
3. S-T-D: Season-to-date
4. Q-T-D: Quarter-to-date
5. P-T-D: Period-to-date
6. M-T-D: Month-to-date
7. W-T-D: Week-to-date
8. D-T-D: Day-to-date
Using all 8 DTS members is not required, but the intended period hierarchy should be set-up upon initial implementation to enable the DTS members. For example, the Period hierarchy below has the YearTotal
as the 1st generation, the Qtr as 2nd generation, and Period as the 3rd generation in the hierarchy. Once the Period's primary hierarchy is developed it cannot be edited.
With this detail, the Y-T-D, Q-T-D, and M-T-D dynamic time series can be enabled.
Alternate Dynamic Series Option
As mentioned in the previous section, once the period dimension is set up, it cannot be changed. However, member formulas can be used to create the Dynamic Time Series if it is later needed.
In the example below, the hierarchy did not include the Quarters during the initial implementation, therefore the Q-T-D DTS cannot be enabled.
However, an alternate hierarchy can be created with member formulas for QTD. For accounts with the account type "Balance," you can create a UDA to calculate the Balance member based on the current period.
This approach is less efficient than using DTS but is an option if the period setup has changed since implementation and requires more detail.