Essbase optimizes calculation performance by calculating formulas for a range of members in the same dimension simultaneously. Some formulas, however, require values from members of the same dimension, and Essbase may not yet have calculated the required values.
A good example is that of cash flow, in which the opening inventory is dependent on the ending inventory from the previous month.
The values for Opening Inventory and Ending Inventory must be calculated on a month-by-month basis. Assume you want to achieve the results shown in Table 57:
Assuming that the Opening Inventory value for January is loaded into the database, the following calculations are required to get the results in Table 57:
1. January Ending = January Opening – Sales + Additions 2. February Opening = January Ending 3. February Ending = February Opening – Sales + Additions 4. March Opening = February Ending 5. March Ending = March Opening – Sales + Additions
You can calculate the required results by applying interdependent, multiple equations to one member in the database outline.
The following formula, applied to the Opening Inventory member in the database outline, calculates the correct values:
IF(NOT @ISMBR (Jan)) "Opening Inventory" = @PRIOR("Ending Inventory"); ENDIF; "Ending Inventory" = "Opening Inventory" - Sales + Additions;
If you place the formula in a calculation script, you must associate the formula with the Opening Inventory member as shown:
"Opening Inventory" (IF(NOT @ISMBR (Jan)) "Opening Inventory" = @PRIOR("Ending Inventory"); ENDIF;) "Ending Inventory" = "Opening Inventory" - Sales + Additions;
Essbase cycles through the months, performing the following calculations:
The IF statement and @ISMBR function check that the current member on the Year dimension is not Jan. This step is necessary because the Opening Inventory value for Jan is an input value.
If the current month is not Jan, the @PRIOR function obtains the value for the Ending Inventory for the previous month. This value is then allocated to the Opening Inventory of the current month.
To calculate the correct results, you must place the above formula on one member, Opening Inventory. If you place the formulas for Opening Inventory and Ending Inventory on their separate members, Essbase calculates Opening Inventory for all months and then Ending Inventory for all months. This organization means that the value of the Ending Inventory of the previous month is not available when Opening Inventory is calculated. |