In This Section:
The information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases.
Inventory (~) (Label Only) Opening Inventory (+) (TB First) (Expense Reporting) IF(NOT @ISMBR(Jan)) Additions (~) (Expense Reporting) Ending Inventory (~) (TB Last) (Expense Reporting)
QTD (~) @PTD(Apr:May) YTD (~) @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
You can also use Dynamic Time Series members to calculate period-to-date values. See Calculating Time Series Data.
@AVGRANGE(SKIPNONE, Sales, @CURRMBRRANGE(Year, LEV, 0, , 0));
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. See Consolidating #MISSING Values.
Table 62 shows the results when Essbase calculates the cumulative Sales values and places the results in YTD_Sales:
Table 62. Results: Example Calculation Script for Calculating Rolling Values
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.
IF(@ISMBR(Jan)) Asset_MVNT = Assets - Opening_Balance; ELSE Asset_MVNT = Assets - @PRIOR(Assets); ENDIF;
Table 63 shows the results when Essbase calculates the difference between the values of assets in successive months:
Table 63. Results: Example Calculation Script for Calculating Monthly Asset Movements
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 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.
You can test for #MISSING values in a database. 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;
Commission(IF(Sales <> #MISSING) Commission = Sales * .1; ELSE Commission = #MISSING; ENDIF;);
You can perform specific calculations on attribute-dimension members in a database. 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:
See Using Attributes in Calculation Formulas. For more information about the @ATTRIBUTEVAL function, see the Oracle Essbase Technical Reference.