Examples of Formulas

This chapter provides detailed examples of formulas, which you may want to adapt for your own use. For examples of using formulas in calc scripts, see Examples of Calc Scripts.

This chapter includes the following sections:

• Calculating Period-to-Date Values
• Calculating Rolling Values
• Calculating Monthly Asset Movements
• Testing for #MISSING Values
• Calculating an Attribute Formula

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. You can also use Dynamic Time Series members to calculate period-to-date values. For detailed information, see Calculating Time Series Data.

For example, the following figure shows the Inventory branch of the Measures dimension from the Sample Basic database.

Figure 27-1: Inventory Branch from Sample Basic Outline

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

Figure 27-2: Calculating Period-to-Date Values

Assuming that the current month is May, the formula on the QTD member is:

@PTD(Apr:May);

and the formula on the YTD member is:

@PTD(Jan:May);

Hyperion Essbase sums the values for the range of months as appropriate. However, Opening Inventory has a time balance tag, First, and Ending Inventory has a time balance tag, Last. Hyperion Essbase takes these values and treats them accordingly. For more information on time balance tags and other accounts tags, see Calculating Time Series Data.

The following table provides an example of the calculation results for the members in the Inventory branch and for the Sales member:

The values for Sales and Additions have been summed.

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

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

Calculating Rolling Values

You can use the @AVGRANGE function to calculate rolling averages and the @ACCUM function to calculate rolling year-to-date 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.

The formula on the AVG_Sales member is:

@AVGRANGE(SKIPNONE, Sales, @CURRMBRRANGE(Year, LEV, 0, , 0));

and the formula on the YTD_Sales member is:

@ACCUM(Sales);

Hyperion 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. Hyperion Essbase places the results in AVG_Sales. For more information on #MISSING values, see Optimizing Calculations.

Hyperion Essbase calculates the cumulative Sales values and places the results in YTD_Sales.

The following table shows the results:

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.

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 month-by-month 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.

The following example shows three members:

• 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.

The formula on the Asset_MVNT member is:

IF(@ISMBR(Jan)) Asset_MVNT = Assets - Opening_Balance;
ELSE Asset_MVNT = Assets - @PRIOR(Assets);
ENDIF;

The following table shows the results:

Hyperion Essbase cycles through the months, performing the following calculations:

1. The IF statement and @ISMBR function check to see if 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.
2. If the current member on the Year dimension is Jan, Hyperion Essbase subtracts the Opening_Balance from the Jan->Assets value and places the result in Jan->Asset_MVNT.
3. If the current member on the Year dimension is not Jan, the @PRIOR function obtains the value for the previous month's assets. Hyperion 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. For more information on #MISSING values, see Optimizing Calculations.

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 * .9;
ELSE Commission = #MISSING;
ENDIF;

If you place the formula in a calc script, you need to associate it with the commission member as follows:

Commission(IF(Sales <> #MISSING) Commission = Sales * .1;
ELSE Commission = #MISSING;
ENDIF;);

Hyperion Essbase cycles through the database, performing the following calculations:

1. The IF statement checks to see if the value of the Sales member for the current member combination is not #MISSING.
2. If Sales is not #MISSING, Hyperion Essbase multiplies the value in the Sales member by 0.1 and places the result in the Commission member.
3. If Sales is #MISSING, Hyperion Essbase places #MISSING in the Commission member.

Calculating an Attribute Formula

You can perform specific calculations on attribute 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 is:

Profit/@ATTRIBUTEVAL(Ounces);

Hyperion Essbase cycles through the Products dimension, performing the following calculations:

1. 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).
2. Hyperion Essbase then divides Profit by the result of @ATTRIBUTEVAL to yield Profit per Ounce.

Copyright © 1991-2000 Hyperion Solutions Corporation. All rights reserved.