Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
This chapter provides detailed examples of formulas, which you may want to adapt for your own use. For examples of using formulas in calculation scripts, see Reviewing Examples of Calculation Scripts.
The information in this chapter does not apply to aggregate storage outlines. For information about developing formulas in MDX for aggregate storage outline members, see Developing Formulas on Aggregate Storage Outlines.
This chapter includes the following sections:
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 an explanation of how to calculate time series data, see Calculating Time Series Data.
For example, the following figure shows the Inventory branch of the Measures dimension from the Sample Basic database.
Figure 177: Inventory Branch from Sample Basic Outline
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:
QTD (~) @PTD(Apr:May) YTD (~) @PTD(Jan:May);
For example, assuming that the current month is May, you would add this formula to the QTD member:
@PTD(Apr:May);
And you would add this formula on the YTD member:
@PTD(Jan:May);
Analytic Services 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. Analytic Services takes these values and treats them accordingly. For more information on time balance tags, see Calculating First, Last, and Average Values.
The following table provides an example of the calculation results for the members in the Inventory branch and for the Sales member:
Measures -> Time |
Jan |
Feb |
Mar |
Apr |
May |
QTD |
YTD |
---|---|---|---|---|---|---|---|
The values for Sales and Additions have been summed.
Opening Inventory has a First tag. For QTD, Analytic Services takes the first value in the current quarter, which is Apr. For YTD, Analytic Services takes the first value in the year, which is Jan.
Ending Inventory has a Last tag. For QTD, Analytic Services takes the last value in the current quarter, which is May. For YTD, Analytic Services takes the last value in the year, which is also May.
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.
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);
Analytic Services 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. Analytic Services places the results in AVG_Sales. For an explanation of how Analytic Services calculates #MISSING values, see Consolidating #MISSING Values.
This table shows the results when Analytic Services calculates the cumulative Sales values and places the results in YTD_Sales:
Measures -> Time |
Jan |
Feb |
Mar |
Qtr1 |
---|---|---|---|---|
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.
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.
Assume these three members manage the asset values for the database:
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;
This table shows the results when Analytic Services calculates the difference between the values of assets in successive months:
Assets -> Time |
Opening_Balance |
Jan |
Feb |
Mar |
---|---|---|---|---|
Analytic Services cycles through the months, performing these calculations:
You can test for #MISSING values in a database. For an explanation of how Analytic Services calculates #MISSING values, see Consolidating #MISSING Values.
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 need to associate it with the commission member as follows:
Commission(IF(Sales <> #MISSING) Commission = Sales * .1; ELSE Commission = #MISSING; ENDIF;);
Analytic Services cycles through the database, performing the following calculations:
You can perform specific calculations on attribute-dimension members in a database.
Note: For a comprehensive discussion of attribute calculations, see Calculating Attribute Data.
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(@NAME(Ounces));
Analytic Services cycles through the Products dimension, performing the following calculations:
Note: The @NAME function is required to process the string "Ounces" before passing it to the @ATTRIBUTEVAL function.
Note: For an explanation of the functions that you use to perform calculations on attributes in formulas, see Using Attributes in Calculation Formulas. For more information about the @ATTRIBUTEVAL function, see the Technical Reference.
![]() |