Calculating Period-to-Date Values

If the outline includes a dimension tagged as accounts, you can use the @PTD function to calculate period-to-date values.

This example uses the Inventory branch of the Measures dimension from the Sample.Basic database, as shown:

Inventory (~) (Label Only)
   Opening Inventory (+) (TB First) (Expense Reporting) 
      IF(NOT @ISMBR(Jan))
   Additions (~) (Expense Reporting)
   Ending Inventory (~) (TB Last) (Expense Reporting)

To calculate period-to-date values for the year and for the current quarter, add two members to the Year dimension: QTD for quarter-to-date and YTD for year-to-date. For example:

QTD (~) @PTD(Apr:May)
YTD (~) @PTD(Jan:May);

Assuming that the current month is May, add this formula to the QTD member:

@PTD(Apr:May);

And add this formula on the YTD member:

@PTD(Jan:May);

Essbase sums the values for the range of months, as appropriate. Opening Inventory, however, has a time balance tag, First, and Ending Inventory has a time balance tag, Last. Essbase takes these values and treats them accordingly. See Calculating First, Last, and Average Values.

Table 61 provides an example of the calculation results for the members in the Inventory branch and for the Sales member:

Table 61. Results: Example Calculation Script for Calculating Period-to-Date Values

Measures->Time

Jan

Feb

Mar

Apr

May

QTD

YTD

Opening Inventory

100

110

120

110

140

110

100

Additions

110

120

100

160

180

340

670

Sales

100

110

110

130

190

320

640

Ending Inventory

110

120

110

140

130

130

130

The values for Sales and Additions have been summed.

Opening Inventory has a First tag. For QTD, Essbase takes the first value in the current quarter, which is Apr. For YTD, Essbase takes the first value in the year, which is Jan.

Ending Inventory has a Last tag. For QTD, Essbase takes the last value in the current quarter, which is May. For YTD, Essbase takes the last value in the year, which is also May.

Note:

You can also use Dynamic Time Series members to calculate period-to-date values. See Calculating Time Series Data.