Reviewing Examples of Calculation Scripts Skip Navigation
Essbase® Analytic Services Database Administrator's Guide | Update Contents | Previous | Next | Print | ? |
Information Map

Reviewing Examples of Calculation Scripts


The examples in this chapter illustrate different types of calculation scripts, which you may want to adapt for your own use.

This chapter includes the following examples:

Note: Since you do not use calculation scripts with aggregate storage databases, the information in this chapter is not relevant them.

For examples that use the Intelligent Calculation commands SET UPDATECALC and SET CLEARUPDATESTATUS in calculation scripts, see Reviewing Examples That Use SET CLEARUPDATESTATUS and Reviewing Examples and Solutions for Multiple-Pass Calculations.

Calculating Variance

The Sample Basic database includes a calculation of the percentage of variance between Budget and Actual values.

Figure 201: Calculating Variance and Variance %

During a default calculation of the Sample Basic database, Analytic Services aggregates the values on the Market and Product dimensions. Percentage values do not aggregate correctly. Therefore, the Variance % formula needs to be recalculated after the default calculation.

In the Sample Basic outline, Variance % is tagged as a Dynamic Calc, two-pass member. Thus, Analytic Services 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 to recalculate Variance %. For a comprehensive discussion of Dynamic Calc members, see Dynamically Calculating Data Values. See Using Two-Pass Calculation for information about calculation of two-pass members.

Assuming that Intelligent Calculation is turned on (the default), the following calculation script performs a default calculation and then recalculates the formula on Variance %:

CALC ALL;
SET UPDATECALC OFF;
SET CLEARUPDATESTATUS AFTER;
"Variance %"; 
 

Analytic Services performs the following actions:

  1. Analytic Services uses the CALC ALL command to perform a default calculation of the database.

    Note: Alternatively, run a default calculation of the database outline without using a calculation script.

  2. The SET UPDATECALC OFF command turns off Intelligent Calculation.

  3. The CLEARUPDATESTATUS AFTER command tells Analytic Services to mark 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 (by default, data blocks are marked as clean only after a full calculation of the database).

  4. Analytic Services cycles through the database calculating the formula for Variance %.

For information on calculating statistical variance, see the Technical Reference.

For information on using a calculation script for two-pass calculations, see Choosing Two-Pass Calculation Tag or Calculation Script. For a comprehensive discussion on developing and using formulas to calculate a database, see Developing Formulas.

Calculating Database Subsets

In this example, based on the Sample Basic database, the Marketing managers of the regions East, West, South, and Central need to calculate their respective areas of the database.

Figure 202: Market Dimension from the Sample Basic Database

The marketing manager of the region East uses the following calculation script to calculate the data values for East:. Notice how @DESCENDENTS(East) is used to limit the calculations to the Eastern region.

/* 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 
 

The script calculates the Year, Measures, and Product dimensions for each child of East.

Analytic Services performs the following actions:

  1. Analytic Services fixes on the Budget values of the descendants of East.

  2. The Year, Measures, and Product dimensions are calculated in one pass of the database for all Budget values of the descendants of East.

  3. Analytic Services fixes on the Budget values for all members on the other dimensions.

  4. Analytic Services aggregates the descendants of East and places the result in East.

Loading New Budget Values

This example calculates the Budget values of the Sample Basic database and then recalculates the Variance and Variance % members of the database:

/* Calculate all Budget values */
FIX(Budget)
CALC DIM(Year, Product, Market, Measures);
ENDFIX

/* Recalculate the Variance and Variance % formulas, which
      require two passes */
Variance;
"Variance %"; 
 

Analytic Services performs the following actions:

  1. Analytic Services fixes on the Budget values.

  2. Analytic Services calculates all Budget values. The CALC DIM command is used to calculate all the dimensions except for the Scenario dimension, which contains Budget.

  3. Analytic Services calculates the formula applied to Variance in the database outline.

  4. Analytic Services calculates the formula applied to Variance % in the database outline.

Calculating Product Share and Market Share Values

This example, based on the Sample Basic database, calculates product share and market share values for each market and each product.

The product and market share values are calculated as follows:

Assume that you add four members to the Measures dimension-Market Share, Product Share, Market %, and Product %.

/* 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); 
 

Analytic Services performs the following actions:

  1. Analytic Services fixes on the Sales values and consolidates all the Sales values. 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.

  2. Analytic Services cycles through the database and calculates Market Share. It takes the Sales value for each product in each market for each month. It calculates this Sales value as a percentage of total Sales in all markets for each product (Sales -> Market).

  3. Analytic Services calculates Product Share. It takes the Sales value for each product in each market for each month. It calculates this Sales value as a percentage of total Sales of all products in each market (Sales -> Product).

  4. Analytic Services calculates Market %. It takes the Sales value for each product in each market for each month. It calculates this Sales value as a percentage of the Sales value of the parent of the current member on the Market dimension. It uses the @PARENTVAL function to obtain the Sales value of the parent on the Market dimension.

  5. Analytic Services calculates Market %. It takes the Sales value for each product in each market for each month. It calculates this Sales value as a percentage of the Sales value of the parent of the current member on the Product dimension. It uses the @PARENTVAL function to obtain the Sales value of the parent on the Product dimension.

Allocating Costs Across Products

The following example is based on the Sample Basic database. It allocates overhead costs to each product in each market for each month.

The overhead costs are allocated based on each product's Sales value as a percentage of the total Sales for all products.

Assume that you add two members to the Measures dimension-OH_Costs for the allocated overhead costs and 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 
 

Analytic Services performs these calculations:

  1. Analytic Services creates a one-dimensional array called ALLOCQ. The size of ALLOCQ is based on the number of members in the Year dimension. Analytic Services uses ALLOCQ to store the value of Sales as a percentage of total Sales temporarily for each member combination.

  2. The SET AGGMISSG OFF; command means that #MISSING values are not aggregated to their parents. Data values stored at parent levels are not overwritten. If this is your system default, you can omit this line. For information on setting the default for aggregating #MISSING values, see Consolidating #MISSING Values.

  3. Analytic Services calculates and consolidates the Measures dimension.

Allocating Values Within or Across Dimensions

Using the @ALLOCATE and @MDALLOCATE functions, you can allocate values to members in the same dimension or to members in multiple dimensions.

Allocating Within a Dimension

The following 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 prior year.

The following example is based on the Sample Basic database. Assume that you have made the following changes to Sample Basic:

Figure 203: Modified Measures and Scenario Dimensions from the Sample Basic Database

For this example, assume that data values of 1000 and 2000 are loaded into Budget -> Total Expenses for Colas and Root Beer, respectively. These values need to be allocated to each expense category, evenly spreading the values based on the non-missing children of Total Expenses from PY Actual. The allocated values need to be rounded to the nearest dollar.

This calculation script defines the allocation:

/* Allocate budgeted total expenses based on prior year */

/* 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 
 

This table shows the results:


.
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 for more information.


Analytic Services cycles through the database, performing the following calculations:

  1. Analytic Services fixes on the children of Total Expenses. Using a FIX statement with @ALLOCATE may improve calculation performance.

  2. For Budget -> Colas -> Marketing, Analytic Services divides 1 by the count of non-missing values for each expense category in PY Actual -> Colas for each month. In this case, 1 is divided by 3, because there are 3 non-missing expense values for Budget -> Colas.

  3. Analytic Services takes the value from step 2 (.333), multiplies it by the value for Budget -> Colas -> Total Expenses (1000), and then rounds to the nearest dollar (333). This value is placed in Budget -> Colas -> Marketing.

  4. Analytic Services repeats steps 2-3 for each expense category for Budget -> Colas and then for Budget -> Root Beer.

  5. As specified in the calculation script, the allocated values are rounded to the nearest whole dollar. Analytic Services 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 the 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).

Allocating Across Multiple Dimensions

The following 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 prior year.

The following example is based on the Sample Basic database. Assume that you have made the following modifications:

For this example, a value of 750 (for Budget -> Total Expenses -> Product -> East -> Jan) needs to 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.

This calculation script defines the allocation:

/* 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 
 

This table shows the values for PY Actual:



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



Analytic Services cycles through the database, performing these calculations:

  1. Analytic Services fixes on East, the children of 100, and Total Expenses. Using a FIX statement with @MDALLOCATE may improve calculation performance.

  2. Before performing the allocation, Analytic Services needs to determine what share of 750 (the value to be allocated) each expense category should receive, for each product-state combination. To determine the share, Analytic Services uses the shares of each expense category from PY Actual. Starting with PY Actual -> 100-10 -> New York, Analytic Services 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, Analytic Services 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.

  3. Analytic Services repeats step 2 for each expense category, for each product-state combination.

  4. During the allocation, Analytic Services uses the percentage shares calculated in step 2 to 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, Analytic Services uses the 20.4% figure calculated in step 2, takes 20.4% of 750 (approximately 153), and places the allocated value in Budget -> 100-10 -> New York -> Marketing (see the next table).

  5. Analytic Services repeats step 4 for each expense category and for each product-state combination, using the percentage shares from PY Actual calculated in step 2 to step 3.

  6. Analytic Services consolidates the expense categories to yield the values for Total Expenses.

This table shows 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.46

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



Goal Seeking Using the LOOP Command

The following example is based on the Sample Basic database. However, the example assumes that no members are tagged as Dynamic Calc and that the Profit per Ounce member (under Ratios in the Scenario dimension) is not included in the calculation. For an explanation of how you calculate values dynamically and how you benefit from doing so, see Dynamic Calc members, in Dynamically Calculating Data Values.

You want to know what sales value you have to reach in order to obtain a certain profit on a specific product.

This example adjusts the Budget value of Sales to reach a goal of 15,000 Profit for Jan. The results are shown for product 100-10.

Figure 204: Measures Dimension from the Sample Basic Database

Assume that the data values before running the goal-seeking calculation script are as follows:


Product, Market, Budget
Jan

Profit

        Margin

                Sales

                COGS

        Total Expenses

                Marketing

                Payroll

                Misc

12,278.50

30,195.50

49,950.00

19,755.00

17,917.00

3,515.00

14,402.00

0

Inventory

Label Only member

Ratios

        Margin %

        Profit %

Label Only member

60.45

24.58



This calculation script produces the goal-seeking results:

/* 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 and base it 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 
 

Analytic Services performs the following calculations:

  1. It declares the required temporary variables using the VAR command. Where appropriate, the initial values are set.

  2. Analytic Services declares a one-dimensional array called Rollback. The size of Rollback is based on the number of members in the Measures dimension. Analytic Services uses Rollback to store the Budget values.

  3. Analytic Services fixes on the Jan -> Budget values for all Product and Market members.

  4. The LOOP command ensures that the commands between LOOP and ENDLOOP are cycled through 35 times for each member combination. However, if the Quit variable is set to 1, then the LOOP is broken and the calculation continues after the ENDLOOP command.

  5. Analytic Services cycles through the member combinations, performing the following calculations:

    1. Analytic Services places the Budget -> Sales value in the Rollback temporary array variable.

    2. It calculates the acceptable error. It multiplies the Target value (15000) by the AcceptableErrorPercent value (0.001) and places the result in the AcceptableError variable.

    3. It retains the current Sales value. It places the Sales value for the current member combination in the PriorVar temporary variable.

    4. It retains the current Profit value. It places the Profit value for the current member combination in the PriorTar temporary variable.

    5. It calculates a new Sales value. It multiplies the PctNewVarChange value (0.1) by the current Sales value, adds the current Sales value, and places the result in Sales.

    6. Analytic Services calculates and consolidates the Measures dimension.

    7. It subtracts the PriorTar value from the PriorVar value and places the result in the DependencyCheck temporary variable.

    8. The IF command checks that DependencyCheck is not 0 (zero).

      • If DependencyCheck is not 0, then Analytic Services subtracts the Target value (15000) from the current Profit and places the result in the CurTarDiff temporary variable.

        The IF command checks to see if the absolute value (irrespective of the + or - sign) of CurTarDiff is greater than the absolute value of the acceptable error (AcceptableError). If it is, Analytic Services calculates the Slope, NxtVar, and PctNewVarChange temporary variables.

        If it is not greater than AcceptableError, Analytic Services breaks the LOOP command by setting the value of Quit to 1. The calculation continues after the ENDLOOP command.

      • If DependencyCheck is 0, Analytic Services places the value in the Rollback array into Budget. Analytic Services breaks the LOOP command by setting the value of Quit to 1. The calculation continues after the ENDLOOP command.

  6. Analytic Services calculates and consolidates the Measures dimension.

The results are shown in this table:


Product, Market, Budget
Jan

Profit

        Margin

                Sales

                COGS

        Total Expenses

                Marketing

                Payroll

                Misc

15,000.00

32,917.00

52,671.50

19,755.00

17,917.00

3,515.00

14,402.00

0

Inventory

Label Only member

Ratios

        Margin %

        Profit %

Label Only member

28.47839913

62.49489762



Forecasting Future Values

The following example uses the @TREND function to forecast sales data for June through December, assuming that data currently exists only up to May. Using the linear regression forecasting method, this example produces a trend, or line, that starts with the known data values from selected previous months and continues with forecasted values based on the known values. In addition, this example demonstrates how to check the results of the trend for "goodness of fit" to the known data values.

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional child, ErrorLR. The goodness-of-fit results are placed in this member. This calculation script defines the forecasting:

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);); 
 

This table explains each parameter:


Parameter
Description

@LIST(Jan,Mar,Apr)

Represents the Ylist, or the members that contain the known data values. The @LIST function is needed to group the three members as a comma-delimited list and to keep the list separate from other parameters.

@LIST(1,3,4)

Represents the Xlist, or the underlying variable values. Since Feb and May are skipped, Analytic Services needs to number the Ylist values accordingly (1,3,4).

,

The extra comma after the Xlist parameter indicates that a parameter has been skipped, in this case, the weightList parameter. The default weight of 1 is used for this example.

@RANGE(ErrorLR, @LIST(Jan,Mar,Apr)

Represents the errorList, or the member list where results of the goodness of fit of the trend line to Ylist are placed. The values placed in errorList are the differences between the data points in Ylist and the data points on the trend line produced. The @RANGE function combines the ErrorLR member with Ylist (Jan, Mar, Apr) to produce a member list.

@LIST(6,7,8,9,10,11,12)

Represents the XforecastList, or the underlying variable values for which the forecast is sought. This example forecasts values consecutively for Jun through Dec, so the values are simply 6,7,8,9,10,11,12.

Jun:Dec

Represents the YforecastList, or the member list into which the forecast values are placed. In this example, values are forecast for Jun through Dec based on the values for Jan, Mar, and Apr.

LR

Specifies the Linear Regression method.



This table shows the results of the calculation script:


.
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



Analytic Services cycles through the database, performing the following calculations:

  1. Analytic Services finds the known data values on which to base the trend (Sales for Jan, Mar, Apr), as specified for the Ylist and Xlist parameters in the calculation script.

  2. Analytic Services calculates the trend line using Linear Regression and places the results in Sales for Jun through Dec, as specified for the YforecastList parameter in the calculation script.

  3. Analytic Services calculates the goodness of fit of the trend line to the data values for Jan, Mar, and Apr and places the results in ErrorLR for those months. For example, the value in ErrorLR for Jan (4.57) means that after Analytic Services 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 since these months were not part of Ylist.



Hyperion Solutions Corporation link