Calculate Rolling Values
You can use the Essbase @AVGRANGE calculation function to calculate rolling averages, and the @ACCUM calculation function to calculate rolling year-to-date values.
For example, assume that a database contains monthly Sales data values, and the outline includes the members AVG_Sales and YTD_Sales.
You would add this formula to the AVG_Sales member:
@AVGRANGE(SKIPNONE, Sales, @CURRMBRRANGE(Year, LEV, 0, , 0));And you would add this formula on the YTD_Sales member:
@ACCUM(Sales);Essbase calculates the average Sales values across the months in the dimension tagged as time. The SKIPNONE parameter means that all values are included, even #MISSING values. Essbase places the results in AVG_Sales.
The following table shows the results when Essbase calculates the cumulative Sales values and places the results in YTD_Sales:
Table 18-2 Results: Example Calculation Script for Calculating Rolling Values
| Measures -> Time | Jan | Feb | Mar | Qtr1 |
|---|---|---|---|---|
|
Sales |
100 |
200 |
300 |
600 |
|
AVG_Sales |
100 |
150 |
200 |
#MISSING |
|
YTD_Sales |
100 |
300 |
600 |
#MISSING |
The values for AVG_Sales are averages of the months-to-date. For example, AVG_Sales -> Mar is an average of Sales for Jan, Feb, and Mar.
The values for YTD_Sales are the cumulative values up to the current month. So YTD_Sales -> Feb is the sum of Sales -> Jan and Sales -> Feb.