19 Reviewing Examples of Formulas for Block Storage Databases
These examples of Essbase formulas can help you develop your own. Examples include periodtodate calculations, rolling values, monthly asset movements, testing for missing/empty values, and attribute calculations.
The information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases.
Calculating PeriodtoDate Values in an Accounts Dimension
If the outline includes a dimension tagged as accounts, you can use the @PTD function to calculate periodtodate 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 periodtodate values for the year and for the current quarter, add two members to the Year dimension: QTD for quartertodate and YTD for yeartodate. 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.
The following is example of the calculation results for the members in the Inventory branch and for the Sales member:
Table 191 Results: Example Calculation Script for Calculating PeriodtoDate 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 periodtodate values.
Calculating Rolling Values
You can use the @AVGRANGE function to calculate rolling averages and the @ACCUM function to calculate rolling yeartodate values.
For example, assume that a database contains monthly Sales data values and that the database 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 192 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 monthstodate. 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.
Calculating Monthly Asset Movements
You can use the @PRIOR function to calculate values based on a previous month’s value.
For example, assume that a database contains assets data values that are stored on a monthbymonth basis. You can calculate the difference between the assets values of successive months (the asset movement) by subtracting the previous month’s value from the present month’s value.
Assume these three members manage the asset values for the database:

Assets for the monthly asset values

Asset_MVNT for the asset movement values

Opening_Balance for the asset value at the beginning of the year
For Jan, the Asset_MVNT value is calculated by subtracting the Opening_Balance value from the Jan value.
You would add this formula on the Asset_MVNT member:
IF(@ISMBR(Jan)) Asset_MVNT = Assets  Opening_Balance;
ELSE Asset_MVNT = Assets  @PRIOR(Assets);
ENDIF;
The following table shows the results when Essbase calculates the difference between the values of assets in successive months:
Table 193 Results: Example Calculation Script for Calculating Monthly Asset Movements
Assets > Time  Opening_Balance  Jan  Feb  Mar 

Assets 
1200 
1400 
1300 
1800 
Asset_MVNT 
200 
100 
500 
Essbase cycles through the months, performing these calculations:

The IF statement and @ISMBR function check whether the current member on the Year dimension is Jan. This check is necessary because the Asset_MVNT value for Jan cannot be calculated by subtracting the previous month’s value.

If the current member on the Year dimension is Jan, Essbase subtracts the Opening_Balance from the Jan > Assets value and places the result in Jan > Asset_MVNT.

If the current member on the Year dimension is not Jan, the @PRIOR function obtains the value for the previous month’s assets. Essbase subtracts the previous month’s assets from the current month’s assets. It places the result in the current month’s Asset_MVNT value.
Testing for #MISSING Values
You can test for #MISSING values in a database.
Assume that a database outline contains a member called Commission. Commission is paid at 10% of sales when the Sales value for the current member combination is not #MISSING. When applied to a Commission member in the database outline, the following formula calculates Commission:
IF(Sales <> #MISSING) Commission = Sales * .1;
ELSE Commission = #MISSING;
ENDIF;
If you place the formula in a calculation script, you must associate it with the Commission member as shown:
Commission(IF(Sales <> #MISSING) Commission = Sales * .1;
ELSE Commission = #MISSING;
ENDIF;);
Essbase cycles through the database, performing the following calculations:

The IF statement checks the value of the Sales member for the current member combination.

If Sales is not #MISSING, Essbase multiplies the value in the Sales member by 0.1 and places the result in the Commission member.

If Sales is #MISSING, Essbase places #MISSING in the Commission member.
Calculating an Attribute Formula
You can perform specific calculations on attributedimension members in a database.
For example, to calculate profitability by ounce for products sized in ounces, you can use the @ATTRIBUTEVAL function in a calculation formula. In the Sample.Basic database, the Ratios branch of the Measures dimension contains a member called Profit per Ounce. The formula on this member:
Profit/@ATTRIBUTEVAL(@NAME(Ounces));
Essbase cycles through the Products dimension, performing the following calculations:

For each base member that is associated with a member from the Ounces attribute dimension, the @ATTRIBUTEVAL function returns the numeric attribute value (for example, 12 for the member 12 under Ounces). The @NAME function is required to process the string “Ounces” before passing it to the @ATTRIBUTEVAL function.

Essbase then divides Profit by the result of @ATTRIBUTEVAL to yield Profit per Ounce.
See also Using Attributes in Calculation Formulas.