Top >
Calculation Functions >
@MDALLOCATE
@MDALLOCATE
Allocates values from a member, from
a cross-dimensional member, or from a value across multiple dimensions.
The allocation is based on a variety of criteria.
Syntax
@MDALLOCATE (amount, Ndim, allocationRange1 ...
allocationRangeN,
basisMbr, [roundMbr], method [, methodParams]
[, round [, numDigits][, roundErr]])
amount |
A value, member, or cross-dimensional member that contains the value
to be allocated into each allocationRange. The value may also
be a constant.
- If amount is a member, the member must be from a dimension
to which an allocationRange belongs.
- If amount is a cross-dimensional member, the member must
include a member from every dimension of every allocationRange.
- If a member or cross-dimensional member is not from an allocationRange
dimension, Analytic Services displays a warning message.
If the amount parameter is a loaded value, it cannot be a Dynamic
Calc member. |
Ndim |
The number of dimensions across which values are allocated. |
allocationRange1 ... allocationRangeN |
Comma-delimited lists of members, member set functions, or range
functions from the multiple dimensions into which values from amount
are allocated. |
basisMbr |
A value, member, or cross-dimensional member that contains the values
that are used as 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. This member (or at least one member of a cross-dimensional
member) must be included in an 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, Analytic Services discards rounding errors.
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 from a member, from
a cross-dimensional member, or from a value across multiple dimensions.
The allocation is based on a variety of criteria.
This function allocates values that are input at an
upper level to lower-level members in multiple dimensions. The allocation
is based upon a specified share or spread of another variable. You can specify
a rounding parameter for allocated values and account for rounding errors.
Notes
- When you use @MDALLOCATE 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 decrease calculation time.
- For a more complex example using the @MDALLOCATE function, see
the Database Administrator's Guide.
- If you have very large allocationRange lists, Analytic Services may return
error messages during the calculation. If you receive error messages,
you may need to raise the number for CALCLOCKBLOCK DEFAULT or use CALCLOCKBLOCK
HIGH in your calculation script.
Example
Consider the following example from the Sample Basic database. A data value
of 500 is loaded to Budget->Total Expenses->East for Jan and Colas.
(For this example, assume that Total Expenses is not a Dynamic Calc member.)
You need to allocate the amount across each expense category for each
child of East. The allocation for each child of East is based on the child's
share of Total Expenses->Actual:
FIX("Total Expenses")
Budget = @MDALLOCATE(Budget->"Total Expenses"->East,2,
@CHILDREN(East),@CHILDREN("Total Expenses"),Actual,,share);
ENDFIX
This example produces the following report:
Jan Colas
Marketing Payroll Misc Total Expenses
========= ======= ==== ==============
Actual New York 94 51 0 145
Massachusetts 23 31 1 55
Florida 53 54 0 107
Connecticut 40 31 0 71
New Hampshire 27 53 2 82
East 237 220 3 460
Budget New York 102.174 55.435 0 #MI
Massachusetts 25 33.696 1.087 #MI
Florida 57.609 58.696 0 #MI
Connecticut 43.478 33.696 0 #MI
New Hampshire 29.348 57.609 2.173 #MI
East #MI #MI #MI 500
See Also
@ALLOCATE