In This Section:
For examples that use the Intelligent Calculation commands SET UPDATECALC and SET CLEARUPDATESTATUS, see Reviewing Examples That Use SET CLEARUPDATESTATUS and Reviewing Examples and Solutions for Multiple-Pass Calculations.
Figure 135, Variance and Variance % in the Scenario Dimension shows an outline in which Variance and Variance % are tagged as Dynamic Calc, two-pass members.
During a default calculation, Essbase aggregates the values on the Market and Product dimensions. Because percentage values do not aggregate correctly, the Variance % formula must be recalculated after the default calculation.
Because Variance % is tagged as a Dynamic Calc, two-pass member, Essbase dynamically calculates Variance % values when they are retrieved. The dynamic calculation overwrites the incorrect values with the correctly calculated percentages.
If you choose not to tag Variance % as a Dynamic Calc, two-pass member, use the following calculation script—which assumes that Intelligent Calculation is turned on (the default)—to perform a default calculation and to recalculate the formula on Variance %:
CALC ALL; SET UPDATECALC OFF; SET CLEARUPDATESTATUS AFTER; "Variance %";
Alternatively, you can run a default calculation of the database outline without using a calculation script.
Marks the calculated blocks calculated by the variance formula of the calculation script as clean, even though the variance calculation is a partial calculation of the database (CLEARUPDATESTATUS AFTER).
By default, data blocks are marked as clean only after a full calculation of the database.
This example shows how a regional Marketing manager can calculate her respective area of the database. The calculation script uses @DESCENDENTS(East) to limit the calculations to the East region, as it calculates the Year, Measures, and Product dimensions for each child of East.
Figure 136, East, West, South, and Central Members in the Market Dimension shows an outline of the East, West, South, and Central members in the Market dimension:
/* Calculate the Budget data values for the descendants of East */ FIX(Budget, @DESCENDANTS(East)) CALC DIM(Year, Measures, Product); ENDFIX /* Consolidate East */ FIX(Budget) @DESCENDANTS(East); ENDFIX
/* Calculate all Budget values */ FIX(Budget) CALC DIM(Year, Product, Market, Measures); ENDFIX /* Recalculate the Variance and Variance % formulas, which requires two passes */ Variance; "Variance %";
The CALC DIM command is used to calculate all the dimensions except for the Scenario dimension, which contains Budget.
/* First consolidate the Sales values to ensure that they are accurate */ FIX(Sales) CALC DIM(Year, Market, Product); ENDFIX /* Calculate each market as a percentage of the total market for each product */ "Market Share" = Sales % Sales -> Market; /* Calculate each product as a percentage of the total product for each market */ "Product Share" = Sales % Sales -> Product; /* Calculate each market as a percentage of its parent for each product */ "Market %" = Sales % @PARENTVAL(Market, Sales); /* Calculate each product as a percentage its parent for each market */ "Product %" = Sales % @PARENTVAL(Product, Sales);
The CALC DIM command is used to calculate the Year, Market, and Product dimensions. The Measures dimension contains the Sales member and therefore is not consolidated. The Scenario dimension is label only and therefore does not need to be consolidated.
Cycles through the database and calculates Market Share by taking the Sales value for each product in each market for each month and calculating this Sales value as a percentage of total Sales in all markets for each product (Sales -> Market).
Calculates Product Share by taking the Sales value for each product in each market for each month and calculating this Sales value as a percentage of total Sales of all products in each market (Sales -> Product).
Calculates Market % by taking the Sales value for each product in each market for each month and calculating this Sales value as a percentage of the Sales value of the parent of the current member on the Market dimension.
The @PARENTVAL function is used to obtain the Sales value of the parent on the Market dimension.
Calculates Product % by taking the Sales value for each product in each market for each month, and calculating this Sales value as a percentage of the Sales value of the parent of the current member on the Product dimension.
The @PARENTVAL function is used to obtain the Sales value of the parent on the Product dimension.
This example allocates overhead costs to each product in each market for each month. Overhead costs are allocated based on each product’s Sales value as a percentage of the total Sales for all products.
OH_Costs for the allocated overhead costs
OH_TotalCost for the total overhead costs
/* Declare a temporary array called ALLOCQ based on the Year dimension */ ARRAY ALLOCQ[Year]; /* Turn the Aggregate Missing Values setting off. If this is your system default, omit this line */ SET AGGMISSG OFF; /* Allocate the overhead costs for Actual values */ FIX(Actual) OH_Costs (ALLOCQ=Sales/Sales->Product; OH_Costs = OH_TotalCost->Product * ALLOCQ;); /* Calculate and consolidate the Measures dimension */ CALC DIM(Measures); ENDFIX
The size of ALLOCQ is based on the number of members in the Year dimension.
If SET AGGMISSG OFF is your system default, omit this line. See Consolidating #MISSING Values.
Takes the Sales value for each product in each market for each month and calculates it as a percentage of total Sales for all products in each market (Sales -> Product). The result is placed in ALLOCQ.
This example uses the @ALLOCATE function to allocate budgeted total expenses across expense categories for two products. The budgeted total expenses are allocated based on the actual values for the previous year.
Assume that you made the following changes, as shown in the outline in Figure 137, Modified Measures and Scenario Dimensions:
Assume that data values of 1000 and 2000 are loaded into Budget -> Total Expenses for Colas and Root Beer, respectively. These values must be allocated to each expense category, evenly spreading the values based on the nonmissing children of Total Expenses from PY Actual. The allocated values must be rounded to the nearest dollar.
/* Allocate budgeted total expenses based on prior year */ FIX("Total Expenses") Budget = @ALLOCATE(Budget->"Total Expenses", @CHILDREN("Total Expenses"),"PY Actual",, spread,SKIPMISSING,roundAmt,0,errorsToHigh) ENDFIX
Budget PY Actual Colas Marketing 334 * 150 Payroll #MI #MI Lease 333 200 Misc 333 100 Total Expenses 1000 450 Root Beer Marketing 500 300 Payroll 500 200 Lease 500 200 Misc 500 400 Total Expenses 2000 1100
* Rounding errors are added to this value. See step 5.
Essbase cycles through the database, performing the following calculations:
Using a FIX statement with @ALLOCATE may improve calculation performance.
In this case, 1 is divided by 3, because there are 3 nonmissing expense values for Budget -> Colas.
Takes the value from step 2 (.333), multiplies it by the value for Budget -> Colas -> Total Expenses (1000), and rounds to the nearest dollar (333). The result is placed in Budget -> Colas -> Marketing.
Essbase makes a second pass through the block to make the sum of the rounded values equal to the allocation value (for example, 1000 for Budget -> Colas -> Total Expenses). In this example, there is a rounding error of 1 for Budget -> Colas -> Total Expenses, because the expense categories add up to 999, not 1000, which is the allocation value. Because all allocated values are identical (333), the rounding error of 1 is added to the first value in the allocation range, Budget -> Colas -> Marketing (thus a value of 334).
This example uses the @MDALLOCATE function to allocate a loaded value for budgeted total expenses across three dimensions. The budgeted total expenses are allocated based on the actual values of the previous year.
For this example, a value of 750 (for Budget -> Total Expenses -> Product -> East -> Jan) must be allocated to each expense category for the children of product 100 across the states in the East. The allocation uses values from PY Actual to determine the percentage share that each category should receive.
/* Allocate budgeted total expenses based on prior year, across 3 dimensions */ SET UPDATECALC OFF; FIX (East, “100”, “Total Expenses”) BUDGET = @MDALLOCATE(750,3,@CHILDREN(“100”),@CHILDREN("Total Expenses"),@CHILDREN(East),"PY Actual",,share); ENDFIX
Jan PY Actual Marketing Payroll Misc Total Expenses 100–10 New York 94 51 0 145 Massachusetts 23 31 1 55 Florida 27 31 0 58 Connecticut 40 31 0 71 New Hampshire 15 31 1 47 100-20 New York 199 175 2 376 Massachusetts #MI #MI #MI #MI Florida #MI #MI #MI #MI Connecticut 26 23 0 49 New Hampshire #MI #MI #MI #MI 100-30 New York #MI #MI #MI #MI Massachusetts 26 23 0 49 Florida #MI #MI #MI #MI Connecticut #MI #MI #MI #MI New Hampshire #MI #MI #MI #MI 100 New York #MI #MI #MI #MI Massachusetts 12 22 1 35 Florida 12 22 1 35 Connecticut 94 51 0 145 New Hampshire 23 31 1 55 East 237 220 3 460
Essbase cycles through the database, performing these calculations:
Using a FIX statement with @MDALLOCATE may improve calculation performance.
Before performing the allocation, determines what share of 750 (the value to be allocated) each expense category should receive, for each product-state combination, using the shares of each expense category from PY Actual. Starting with PY Actual -> 100-10 -> New York, Essbase divides the value for the first expense category, Marketing, by the value for PY Actual-> 100-10 -> East -> Total Expenses to calculate the percentage share of that category.
For example, Essbase divides the value for PY Actual -> 100-10 -> New York -> Marketing (94) by the value for PY Actual -> 100-10 -> East -> Total Expenses (460), which yields a percentage share of approximately 20.4% for the Marketing category.
Repeats step 2 for each expense category, for each product-state combination.
During the allocation, Essbase uses the percentage shares calculated in step 2 and step 3 to determine what share of 750 should be allocated to each child of Total Expenses from Budget, for each product-state combination.
For example, for Marketing, Essbase uses the 20.4% figure calculated in step step 2, takes 20.4% of 750 (approximately 153), and places the allocated value in Budget -> 100-10 -> New York -> Marketing (see the results that follow this procedure).
The results of the allocation for Budget:
Jan Budget Marketing Payroll Misc Total Expenses 100–10 New York 153.26 83.15 0 236.41 Massachusetts 37.50 50.54 1.63 89.67 Florida 44.02 50.54 0 94.56 Connecticut 65.22 50.54 0 115.76 New Hampshire 24.26 50.54 1.63 76.63 100-20 New York #MI #MI #MI #MI Massachusetts #MI #MI #MI #MI Florida 42.39 37.50 0 79.89 Connecticut #MI #MI #MI #MI New Hampshire #MI #MI #MI #MI 100-30 New York #MI #MI #MI #MI Massachusetts #MI #MI #MI #MI Florida #MI #MI #MI #MI Connecticut #MI #MI #MI #MI New Hampshire 19.57 35.87 1.63 57.07 100 New York 153.26 83.15 0 236.41 Massachusetts 37.50 50.54 1.63 89.67 Florida 86.41 88.04 0 174.46 Connecticut 65.22 50.54 0 115.76 New Hampshire 44.02 86.41 3.26 133.70 East 386.41 358.70 4.89 750
This example shows how to calculate the sales value you must reach to obtain a certain profit on a specific product. In this case, the calculation script adjusts the Budget value of Sales to reach a goal of 15,000 Profit for Jan.
As shown in the outline in Figure 138, Measures Dimension, assume that no members are tagged as Dynamic Calc, and that the Profit per Ounce member (under Ratios in the Measures dimension) is not included in the calculation.
Product, Market, Budget Jan Profit 12,278.50 Margin 30,195.50 Sales 49,950.00 COGS 19,755.00 Total Expenses 17,917.00 Marketing 3,515.00 Payroll 14,402.00 Misc 0 Inventory Label Only member Ratios Label Only member Margin % 60.45 Profit % 24.58
/* Declare the temporary variables and set their initial values*/ VAR Target = 15000, AcceptableErrorPercent = .001, AcceptableError, PriorVar, PriorTar, PctNewVarChange = .10, CurTarDiff, Slope, Quit = 0, DependencyCheck, NxtVar; /*Declare a temporary array variable called Rollback based on the Measures dimension */ ARRAY Rollback [Measures]; /* Fix on the appropriate member combinations and perform the goal-seeking calculation*/ FIX(Budget, Jan, Product, Market) LOOP (35, Quit) Sales (Rollback = Budget; AcceptableError = Target * (AcceptableErrorPercent); PriorVar = Sales; PriorTar = Profit; Sales = Sales + PctNewVarChange * Sales;); CALC DIM(Measures); Sales (DependencyCheck = PriorVar - PriorTar; IF(DependencyCheck <> 0) CurTarDiff = Profit - Target; IF(@ABS(CurTarDiff) > @ABS(AcceptableError)) Slope = (Profit - PriorTar) / (Sales - PriorVar); NxtVar = Sales - (CurTarDiff / Slope); PctNewVarChange = (NxtVar - Sales) / Sales; ELSE Quit = 1; ENDIF; ELSE Budget = Rollback; Quit = 1; ENDIF;); ENDLOOP CALC DIM(Measures); ENDFIX
Essbase performs the following calculations:
The size of Rollback is based on the number of members in the Measures dimension.
Ensures that the commands between LOOP and ENDLOOP are cycled through 35 times for each member combination. If, however, the Quit variable is set to 1, the LOOP is broken and the calculation continues after the ENDLOOP command.
Places the Budget -> Sales value in the Rollback temporary array variable.
Calculates the acceptable error, by multiplying the Target value (15000) by the AcceptableErrorPercent value (0.001). The result is placed in the AcceptableError variable.
If greater than AcceptableError, calculates the Slope, NxtVar, and PctNewVarChange temporary variables.
If not greater than AcceptableError, breaks the LOOP command by setting the value of Quit to 1. The calculation continues after the ENDLOOP command.
Product, Market, Budget Jan Profit 15,000.00 Margin 32,917.00 Sales 52,671.50 COGS 19,755.00 Total Expenses 17,917.00 Marketing 3,515.00 Payroll 14,402.00 Misc 0 Inventory Label Only member Ratios Label Only member Margin % 28.47839913 Profit % 62.49489762
This example uses a linear regression forecasting method to produce a trend (@TREND), or line, that starts with the known data values from selected previous months and continues with forecasted values based on the known values, and shows how to check the results of the trend for “goodness of fit” to the known data values. In this case, the calculation script forecasts sales data for June–December, assuming that data currently exists only up to May.
Sales (@TREND(@LIST(Jan,Mar,Apr),@LIST(1,3,4),, @RANGE(ErrorLR,@LIST(Jan,Mar,Apr)), @LIST(6,7,8,9,10,11,12), Jun:Dec,LR););
Table 89 describes the parameters used in the forecasting calculation script:
Table 89. Parameters Used in the Example Calculation Script for Forecasting Future Values
The @LIST function groups the three members as a comma-delimited list and keeps the list separate from other parameters.
This example uses a default weight of 1.
The values placed in errorList are the differences between the data points in Ylist and the data points on the trend line that is produced.
The @RANGE function combines the ErrorLR member with Ylist (Jan, Mar, Apr) to produce a member list.
Essbase cycles through the database, performing the following calculations:
For example, the value in ErrorLR for Jan (4.57) means that after Essbase calculates the trend line, the difference between the Sales value for Jan (2339) and the Jan value on the trend line is 4.57. The ErrorLR values for Feb and May are #MISSING, because these months were not part of Ylist.
100 West Actual Sales ErrorLR Jan 2339 4.57 Feb 2298 #MI Mar 2313 -13.71 Apr 2332 9.14 May 2351 #MI Jun 2315.14 #MI Jul 2311.29 #MI Aug 2307.49 #MI Sep 2303.57 #MI Oct 2299.71 #MI Nov 2295.86 #MI Dec 2292 #MI