Allocate Values Across Multiple Dimensions
This Essbase calculation script 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.
Assume that you made the following changes:
-
Added a child, PY Actual, to the Scenario dimension
-
Copied data from Actual into PY Actual
-
Cleared data from Budget
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.
Example script:
/* 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);
ENDFIXThe 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 460Essbase cycles through the database, performing these calculations:
-
Fixes on East, the children of 100, and Total Expenses.
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 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).
-
Repeats step 4 for each expense category and for each product-state combination, using the percentage shares from PY Actual calculated in step 2 and step 3.
-
Consolidates the expense categories to yield the values for Total Expenses.
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