Allocate Values within a Dimension

This Essbase calculation script 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 to Sample Basic, as shown in the outline illustrated below:

Added a child, Lease, under Total Expenses in the Measures dimension
Added a child, PY Actual, to the Scenario dimension

Figure 24-3 Modified Measures and Scenario Dimensions

Sample Basic outline section, with 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.

Example script:

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

The results of the calculation script:

                             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.

Essbase cycles through the database, performing the following calculations:

Fixes on the children of Total Expenses.

Using a FIX statement with @ALLOCATE may improve calculation performance.
For Budget -> Colas -> Marketing, divides 1 by the count of nonmissing values for each expense category in PY Actual -> Colas for each month.

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.
Repeats steps 2 and 3 for each expense category for Budget -> Colas and then for Budget -> Root Beer.
As specified in the calculation script, rounds allocated values to the nearest whole dollar.

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