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.
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:
Note: Alternatively, run a default calculation of the database outline without using a calculation script.
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.
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:
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:
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:
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:
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. Notice that both of the equations are enclosed in parentheses ( ) and associated with the OH_Costs member, OH_Costs (equation1; equation2;).
Using the @ALLOCATE and @MDALLOCATE functions, you can allocate values to members in the same dimension or to members in multiple dimensions.
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
| Budget |
PY Actual |
||
|---|---|---|---|
|
* Rounding errors are added to this value. See step 5 for more information. |
|||
Analytic Services cycles through the database, performing the following calculations:
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 |
|
Analytic Services cycles through the database, performing these calculations:
This table shows the results of the allocation for Budget:
| |
Jan Budget |
||||
|---|---|---|---|---|---|
| |
Marketing |
Payroll |
Misc |
Total Expenses |
|
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 |
|---|---|
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:
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.
The results are shown in this table:
| Product, Market, Budget |
Jan |
|---|---|
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:
This table shows the results of the calculation script:
| 100 West Actual |
||
|---|---|---|
| Sales |
ErrorLR |
|
Analytic Services cycles through the database, performing the following calculations: