Top >
Calculation Functions >
@ALLOCATE
@ALLOCATE
Allocates values from a member, from a cross-dimensional member,
or from a value across a member list. The allocation is based on a
variety of criteria.
Syntax
@ALLOCATE (amount, allocationRange, basisMbr, [roundMbr],
method
[, methodParams] [, round [, numDigits][, roundErr]])
amount
|
A value, member, or cross-dimensional member that contains the value to be allocated into allocationRange. The value may also be a constant.
- If amount is a member, the member must be from the dimension to which
allocationRange
belongs.
- If amount is a cross-dimensional member, at least one of its members
must be from the dimension to which allocationRange belongs.
- If no member or cross-dimensional member is from the dimension to
which allocationRange belongs, a warning message is displayed.
If the amount parameter is a loaded value, it cannot be a Dynamic Calc member.
|
allocationRange
|
A comma-delimited list of members, member set functions, or range functions,
into which value(s) from amount are allocated.
allocationRange should be from only one level
(for example, @CHILDREN(Total Expenses) rather than from multiple levels
(for example, @DESCENDANTS(Product)).
|
basisMbr
|
A value, member, or cross-dimensional member that contains the
values that provide the basis for the allocation. The method
you specify determines how the basis data is used.
|
roundMbr
|
Optional. The member or cross-dimensional member to which rounding errors
are added. The member (or at least one member of a cross-dimensional member)
must be included in allocationRange.
|
method
|
The expression that determines how values are allocated. One of the
following:
- share:
Uses basisMbr to calculate a percentage share. The percentage
share is calculated by dividing the value in basisMbr for the
current member in allocationRange by the sum across the allocationRange
for that basis member:
amount * (@CURRMBR()->basisMbr/@SUM(allocationRange->
basisMbr)
- spread:
Spreads amount across allocationRange:
amount * (1/@COUNT(SKIP, allocationRange))
SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH: Values to be ignored during
calculation of the spread. You must specify a SKIP parameter only for
spread.
- SKIPNONE: Includes all cells.
- SKIPMISSING: Excludes all
#MISSING values in basisMbr,
and stores #MISSING for values in allocationRange for
which the basisMbr is missing.
- SKIPZERO: Excludes all zero (0) values in basisMbr, and stores
#MISSING for values in allocationRange for which
the basisMbr is zero.
- SKIPBOTH: Excludes all zero (0) values and all
#MISSING
values, and stores #MISSING for values in allocationRange
for which the basisMbr is zero (0) or #MISSING .
- percent: Takes a percentage value from basisMbr for each
member in allocationRange and applies the percentage value to
amount:
amount * (@CURRMBR()->basisMbr * .01)
- add: Takes the value from basisMbr for each member of
allocationRange and adds the value to amount:
amount + @CURRMBR()->basisMbr
- subtract: Takes the value from basisMbr for each
member of allocationRange and subtracts the value from
amount:
amount - @CURRMBR()->basisMbr
- multiply: Takes the value from basisMbr for each
member of allocationRange and multiplies the value by amount:
amount * @CURRMBR()->basisMbr
- divide: Takes the value from basisMbr for each member of
allocationRange and divides the value by amount:
amount/@CURRMBR()->basisMbr
|
round
|
Optional. One of the following:
- noRound: No rounding. noRound is the default.
- roundAmt: Indicates that you want to round the allocated values.
If you specify roundAmt, you also must specify numDigits to
indicate the number of decimal places to round to.
|
numDigits
|
An integer that represents the number of decimal places to round to.
You must specify numDigits if you specify roundAmt.
- If numDigits is 0, the allocated values are rounded to the nearest integer.
The default value for numDigits is 0.
- If numDigits is greater than 0, the allocated values are rounded to the
specified number of decimal places.
- If numDigits is a negative value, the allocated values are rounded to a power of 10.
If you specify roundAmt, you also can specify a roundErr parameter.
|
roundErr
|
Optional. An expression that specifies where rounding errors should be placed.
You must specify roundAmt in order to specify roundErr. If you do not
specify roundErr, rounding errors are discarded.
To specify roundErr, choose from one of the following:
- errorsToHigh: Adds rounding errors to the member with the highest
allocated value. If allocated values are identical, adds rounding errors to
the first value in allocationRange.
- errorsToLow: Adds rounding errors to the member with the lowest allocated value.
If allocated values are identical, adds rounding errors to the first value
in allocationRange.
#MISSING is treated as the lowest value in a
list; if multiple values are #MISSING , rounding errors are added to the
first #MISSING value in the list.
- errorsToMbr: Adds rounding errors to the specified roundMbr, which
must be included in allocationRange.
|
Description
This function allocates values that are input at an upper level to lower-level
members. The allocation is based upon a specified share or spread of another variable. For
example, you can allocate values loaded to a parent member to all of that member's children. You can specify a rounding parameter for allocated values and account for rounding errors.
Notes
- When you use @ALLOCATE in a calculation script, use it within a FIX
statement; for example, FIX on the member to which the allocation amount
is loaded. Although FIX is not required, using it may improve calculation
performance.
- If you use @ALLOCATE in a member formula, your formula should look like this:
Member Name = @ALLOCATE (...)
This is because allocation functions never return a value; rather, they calculate a
series of values internally based on the range specified.
- For an example that explains the use of rounding error processing with
the @ALLOCATE function, see the Database Administrator's Guide.
Example
Consider the following example from the Sample Basic database. The example assumes that the
Scenario dimension contains an additional member, PY Actual, for the prior year's actual
expenses. Data values of 7000 and 8000 are loaded into Budget->Total Expenses for Jan
and Feb, respectively. (For this example, assume that Total Expenses is not a Dynamic Calc member.)
You need to allocate values to each expense category (to each child of Total Expenses).
The allocation for each of child of Total Expenses is based on the child's share of
actual expenses for the prior year (PY Actual).:
FIX("Total Expenses")
Budget = @ALLOCATE(Budget->"Total Expenses",@CHILDREN("Total Expenses"),
"PY Actual",,share);
ENDFIX
This example produces the following report:
Product Market
PY Actual Budget
Jan Feb Jan Feb
=== === === ===
Marketing 5223 5289 3908.60 4493.63
Payroll 4056 4056 3035.28 3446.05
Misc 75 71 56.13 60.32
Total Expenses 9354 9416 7000 8000
See Also
@MDALLOCATE