@ATTRIBUTEBVAL @ATTRIBUTESVAL @CALCMODE @CONCATENATE @EXP @IRDESCENDANTS @LN @LOG |
@LOG10 @MAXS @MAXSRANGE @MEMBER @MINS @MINSRANGE @RDESCENDANTS @SUBSTRING |
@ALIAS @NAME @NEXTS @PRIORS @SHIFTMINUS @SHIFTPLUS |
Member set functions can now be used within a FIX statement and in partition area definitions. Member set functions also support the cross-dimensional operator. See the List of Member Set Functions.
The @ABS() function returns the absolute value of expression. The absolute value of a number is that number less its sign. A negative number becomes positive, while a positive number remains positive.
@ABS(expression)
expression | Member name or mathematical expression that generates a numeric value. |
The following example is based on the Demo Basic database. In this example, Variance needs to be presented as a positive number. The @ABS function is used because otherwise some combinations of Actual - Budget would return negative values.
Variance=@ABS(Actual-Budget);
This example produces the following report:
Sales VCR San_Francisco Jan Feb Mar === === === Actual 1,323 1,290 1,234 Budget 1,200 1,100 1,100 Variance 123 190 134
@INT
@REMAINDER
@ROUND
@TRUNCATE
The @ACCUM() function accumulates the values of mbrName within rangeList, up to the current member in the dimension of which rangeList is a part.
@ACCUM (mbrName [, rangeList])
mbrName | Any valid single member name or member combination (or a function that returns a single member or member combination) whose value is to be accumulated. |
rangeList | Optional comma-delimited list of members, member set functions, or range functions from the same dimension, across which the accumulation occurs. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
In this example, Accum_Asset is calculated using the following formula:
"Accum Asset" = @ACCUM(Asset, FY1992:FY1995);
This example produces the following report. This report shows that the values for Asset are accumulated starting with FY1992 and the accumulated values are placed in Accum_Asset for FY1992 through FY1995:
FY1990 FY1991 FY1992 FY1993 FY1994 FY1995 ======= ======= ======= ======= ======= ======= Asset 9,000 0 1,000 0 2 ,500 1,500 Residual 750 0 0 0 #MISSING #MISSING Life 5 0 3 0 #MISSING #MISSING Accum_Asset #MISSING #MISSING 1,000 1,000 3,500 5,000
The @ALIAS() function takes a string as an argument and returns an alias name to the function that calls @ALIAS.
@ALIAS (function_name)
Because functions that take strings as arguments may not function correctly if the string matches a member alias, use function @ALIAS to pass member alias names as strings, for example when passing alias names as strings to functions such as @ISUDA, @UDA, @CONCATENATE, @SUBSTRING, @MATCH, or @NAME.
For example, if the value "US$" is both an alias and a user-defined attribute, pass the string using @ALIAS:
IF(@ISUDA(@ALIAS("US$"))) ... ENDIF
The @ALLANCESTORS() function returns all ancestors of the specified member, including ancestors of any occurrences of the specified member as a shared member. This function excludes the specified member.
You can use @ALLANCESTORS as a parameter of another function, where that parameter is a list of members.
@ALLANCESTORS(mbrName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
The following example is based on the Sample Basic database. Sample Basic has a shared level of diet drinks, which includes 100-20 (Diet Cola). So 100-20 (Diet Cola) is a descendant of 100 (Colas) and is a shared member descendant of Diet:
100 100-10 100-20 Diet 100-20 (Shared Member)
The following calculation script increases by 5% the Budget->Sales values of all ancestors of 100-20, including Diet.
FIX(Budget,@ALLANCESTORS("100-20")) Sales = Sales * 1.05; ENDFIX
This example produces the following report. This report shows that the Budget->Sales values for 100, Diet, and Product (the ancestors of 100-20) have been increased by 5%. The original values were 8980, 8260, and 28480, respectively.
Jan Actual Budget Sales Sales ===== ===== Market 100-10 4860 5200 100-20 2372 2610 100-30 1082 1170 100 8314 9429 * 100-20 2372 2610 200-20 3122 3090 300-30 2960 2560 Diet 8454 8673 * Product 31538 30954 *
@IALLANCESTORS, which includes the specified member.
The @ALLOCATE() function allocates values from a member, from a cross-dimensional member, or from a value across a member list within the same dimension. The allocation is based on a variety of criteria.
The @ALLOCATE 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.
@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.
|
allocationRange | A comma-delimited list of members, member set functions, or range functions from the same dimension, 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:
|
round | Optional. One of the following:
|
numDigits | An integer that represents the number of decimal places to round to.
You must specify numDigits if you specify roundAmt.
|
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, Essbase discards rounding errors.
To specify roundErr, choose from one of the following:
|
Member Name = @ALLOCATE (...)
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
The @ANCEST() function returns the ancestor at the specified generation or level of the current member being calculated in the specified dimension. If you specify the optional mbrName, that ancestor is combined with the specified member.
This member set function can be used as a parameter of another function, where that parameter is a member or list of members.
@ANCEST(dimName, genLevNum [, mbrName])
dimName | Single dimension name specification. |
genLevNum | An integer value that defines the generation or level number from which the ancestor value is returned. A positive integer defines a generation number. A value of 0 or a negative integer defines a level number. |
mbrName | Optional. Any valid single member name or member combination, or a function that returns a single member or member combination, that is crossed with the ancestor returned. |
Sales(@ANCEST(Product) = 5;);
Sales = @ANCEST(Product,2); Sales = @ANCESTVAL(Product,2);In this case, using the latter formula results in better calculation performance. In general, use @ANCEST as a member rather than as an implied value of a cell. For example:
Sales = @AVG(SKIPMISSING, @ISIBLINGS(@ANCEST(Product,2)));
In the Sample Basic database:
Function Generated List @ANCEST(Product,2,Sales) Colas->Sales, if the current member of Product being calculated is Diet Cola. @ANCEST(Measures,3,East) Total Expenses->East, if the current member of Measures being calculated is Payroll.
@PARENT
@CHILDREN
@ANCESTORS
@DESCENDANTS
@SIBLINGS
The @ANCESTORS() function returns all ancestors of the specified member (mbrName) or those up to a specified generation or level. You can use this member set function as a parameter of another function, where that parameter is a list of members.
@ANCESTORS (mbrName [, genLevNum | genLevName])
mbrName | Any valid single member name or member combination (or a function that returns a single member or member combination). |
genLevNum | Optional. An integer value that defines the absolute generation or level number up to which to select the members. A positive integer defines a generation number. A value of 0 or a negative integer defines a level number. |
genLevName | Optional. Level name or generation name up to which to select the members. |
@ANCESTORS(200-30)
, Essbase returns 200, Product (in that order).
This order is important to consider when you use the @ANCESTORS member set function with
certain forecasting and statistical functions.
In the Sample Basic database:
@ANCESTORS("New York")
returns East, Market (in that order).
@ANCESTORS(Qtr4)
returns Year.
@ANCESTORS("100-10",1)
returns 100, Product (in that order).
@ANCESTORS(Sales,-2)
returns Margin, Profit (in that order).
@IANCESTORS, which includes the specified member.
@ISANCEST
@CHILDREN
@DESCENDANTS
@SIBLINGS
The @ANCESTVAL() function returns the ancestor values of a specified member combination.
@ANCESTVAL (dimName, genLevNum [, mbrName])
dimName | A single dimension name that defines the focus dimension of ancestor values. |
genLevNum | Integer value that defines the generation or level number from which the ancestor values are to be returned. A positive integer defines a generation reference. A negative number or value of 0 defines a level reference. |
mbrName | Optional. Any valid single member name or member combination (or a function that returns a single member or member combination). |
In this example, SKU Share is derived by taking Sales in each SKU as a percentage of its product family. Families are at generation 2; therefore, each descendant of family is calculated as a percentage its respective ancestor. Consolidated results must be calculated for Sales by Product before the SKU Share calculation occurs.
"SKU Share" = Sales % @ANCESTVAL(Product,2,Sales);
This example produces the following report:
Sales SKU Share ===== ========= SKU101 510 26.0 SKU102 520 26.5 Group01 1030 52.5 SKU120 430 21.9 SKU123 500 25.5 Group02 930 47.4 Family1 1960 100.00
@MDANCESTVAL
@SANCESTVAL
@PARENTVAL
The @ATTRIBUTE() function generates a list of all base members that are associated with the specified attribute member (mbrName). This member set function can be used as a parameter of another function, where that parameter is a member or list of members.
@ATTRIBUTE (attMbrName)
attMbrName | Single attribute member name or member combination. |
@ATTRIBUTE(Large)
returns
all base members that fall into one of the population ranges for the attribute parent Large.
@ATTRIBUTE(Caffeinated_True)
).
@ATTRIBUTE(12_Ounces)
).
In the Sample Basic database,
@ATTRIBUTE(Can);
returns all base members with the Can attribute: Cola, Diet Cola, and Diet Cream.
Consider the following two calculation scripts, which are based on the Sample Basic database:
/* To increase the marketing budget for markets with large populations */ FIX (@ATTRIBUTE(Large)) Marketing = Marketing * 1.1; ENDFIX
/* To calculate the average sales of bottled products */ "Bottle Sales" = @AVG(SKIPBOTH,@ATTRIBUTE(Bottle));
For the current member being calculated, the @ATTRIBUTEBVAL() function returns the associated attribute value from the specified Boolean attribute dimension.
@ATTRIBUTEBVAL(attDimName)
attDimName | The name of a Boolean attribute dimension. |
This example is based on the Sample Basic database.
The Product dimension is associated with the Caffeinated Boolean attribute dimension, as shown in the following example:
Product {Caffeinated} 100 100-10 {Caffeinated:True} 100-20 {Caffeinated:True} 100-30 {Caffeinated:False} 200 200-10 {Caffeinated:True} 200-20 {Caffeinated:True} 200-30 {Caffeinated:False} 200-40 {Caffeinated:False} Caffeinated Attribute {Type: Boolean} True False
For the current member of the base dimension Product, the function
@ATTRIBUTEBVAL(Caffeinated)
returns the associated attribute value from the Boolean attribute dimension, Caffeinated. The following table shows the value that would be returned:
Current Member Return Value 100-10 True 100-20 True 100-30 False 100 #MISSING 200-10 True 200-20 True 200-30 False 200-40 False 200 #MISSING Product #MISSING
For any any member that does not have an associated attribute, Essbase returns #MISSING. Only one value is returned at a time.
For the current member being calculated, the @ATTRIBUTESVAL() function returns the associated attribute value from the specified text attribute dimension.
@ATTRIBUTESVAL(attDimName)
attDimName | The name of a text attribute dimension. |
This example is based on the Sample Basic database.
The Product dimension is associated with the Pkg Type text attribute dimension, as shown in the following example:
Product {Pkg Type} 100 100-10 {Pkg Type:Can} 100-20 {Pkg Type:Can} 100-30 {Pkg Type:Bottle} 200 200-10 {Pkg Type:Bottle} 200-20 {Pkg Type:Bottle} 200-30 {Pkg Type:Bottle} 200-40 {Pkg Type:Bottle} Pkg Type Attribute {Type: Text} Bottle Can
For the current member of the base dimension, Product, the function
@ATTRIBUTESVAL("Pkg Type")
returns the associated attribute value from the text attribute dimension, Pkg Type. The following table shows the value that would be returned:
Current Member Return Value 100-10 Can 100-20 Can 100-30 Bottle 100 (empty string) 200-10 Bottle 200-20 Bottle 200-30 Bottle 200-40 Bottle 200 (empty string) Product (empty string)
For any any member that does not have an associated attribute, Essbase returns an empty string.
For the current member being calculated, the @ATTRIBUTEVAL() function returns the associated attribute value from the specified numeric or date attribute dimension.
@ATTRIBUTEVAL(attDimName)
attDimName | Single dimension specification for a numeric or date attribute dimension. |
#MISSING
.
The following example is based on the Sample Basic database:
"Profit Per Ounce" = Profit/@ATTRIBUTEVAL(Ounces);
In this formula, for the current member being calculated, @ATTRIBUTEVAL returns the associated attribute from the Ounces numeric attribute dimension. For example, if the member being calculated is Cola and if the Ounces attribute value associated with Cola is 12, @ATTRIBUTEVAL returns 12. The value returned is then divided into Profit to yield Profit Per Ounce.
This example produces the following report:
Actual Year West Profit Profit Per Ounce ======== ================ Cola 4593 382.75
The following example applies a specific formula to members with a particular attribute:
Product (IF (@ATTRIBUTEVAL(Caffeinated_True)) Price = Price - Discount; ENDIF;);
If the Product-dimension member being calculated has the Caffeinated_True attribute
value associated with it, @ATTRIBUTEVAL returns 1 (True) and applies the
Price formula to the member. If the current Product member does not have the Caffeinated_True
attribute associated with it, Essbase does not apply the formula and
returns #MISSING
.
@ATTRIBUTEBVAL
@ATTRIBUTESVAL
@TODATE
The @AVG() function returns the average of all values in expList.
@AVG (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)
SKIPNONE | Includes all cells specified in the average operation regardless of their content. |
SKIPMISSING | Excludes all values that are #MISSING in the average operation. |
SKIPZERO | Excludes values of zero from the average calculation. |
SKIPBOTH | Excludes all values of zero or #MISSING from the average calculation. |
expList | Comma-delimited list of member names, variable names, functions, or numeric expressions. expList provides a list of numeric values across which the average is calculated. |
The following example is based on the Sample Basic database. The calculation averages the values for the individual states making up the western region and places the results in West:
FIX(Sales) West=@AVG(SKIPNONE,California:Nevada); ENDFIX
This example produces the following report:
Sales Jan Actual Cola Diet Cola Caffeine Free Cola ==== ========= ================== California 678 118 145 Oregon 160 140 150 Washington 130 190 #MI Utah 130 190 170 Nevada 76 62 #MI West 234.8 140 155
The @AVGRANGE() function returns the average value of the specified member (mbrName) across the specified range (rangeList).
@AVGRANGE( SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, mbrName [, rangeList])
SKIPNONE | Includes all cells specified in the average operation regardless of their content. |
SKIPMISSING | Excludes all values that are #MISSING in the average operation. |
SKIPZERO | Excludes values of zero from the average calculation. |
SKIPBOTH | Excludes all values of zero or #MISSING from the average calculation. |
mbrName | Any valid single member or member combination. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
The following example is based on the Sample Basic database. The calculation script determines the average sales of Colas in the West.
FIX(Sales) West=@AVGRANGE(SKIPNONE,Sales,@CHILDREN(West)); ENDFIX
This example produces the following report:
Sales Colas Actual Jan Feb Mar === === === California 941 899 927 Oregon 450 412 395 Washington 320 362 377 Utah 490 488 476 Nevada 138 137 138 West 467.8 459.6 462.6
The @CALCMODE function enables the choice of an execution mode of a formula. @CALCMODE can control two types of modes:
@CALCMODE (CELL|BLOCK|TOPDOWN|BOTTOMUP)
CELL | Turns on the cell calculation mode |
BLOCK | Turns on the block calculation mode |
TOPDOWN | Turns on the top-down calculation mode |
BOTTOMUP | Turns on the bottom-up calculation mode |
Cell and block modes are mutually exclusive. Top-down and bottom-up modes are mutually exclusive. Within one @CALCMODE specification, you can specify only one option. To specify both types of modes, perform the instruction twice; for example:
@CALCMODE (CELL)
@CALCMODE (TOPDOWN)
Understanding Block Calculation and Cell Calculation Modes
Using block calculation mode, Essbase groups the cells within a block and simultaneously calculates the cells in each group. Block calculation mode is fast, but you must carefully consider data dependencies within the block to ensure that the resulting data is accurate.
Using cell calculation mode, Essbase calculates each cell sequentially, following the calculation order, which is based on the order of the dense dimensions in the outline. For more information on calculation order, see the Database Administrator's Guide.
For more information on which mode Essbase uses and on data dependency issues, see Notes.
Understanding Bottom-Up and Top-Down Calculation Modes
Essbase uses one of two calculation methods to do a full calculation of a database outline: bottom-up calculation (the default) or top-down calculation. If the database outline contains a complex member formula, Essbase performs a top-down calculation for that member. When a formula is compiled, if the formula is to be calculated top-down, Essbase logs a message in the application log file.
For a bottom-up calculation, Essbase determines which existing data blocks need to be calculated before it calculates the database. Essbase then calculates only the blocks that need to be calculated during the full database calculation. The calculation begins with the lowest existing block number and works up through each subsequent block until the last existing block is reached.
In contrast, a top-down calculation calculates the formula on all potential datablocks with the member. A top-down calculation may be less efficient than a bottom-up calculation because more blocks may be calculated than is necessary. Although a top-down calculation is less efficient than a bottom-up calculation, in some cases top-down calculations are necessary to ensure that calculation results are correct. See Example 4.
For more information about bottom-up and top-down calculation modes, see the Database Administrator's Guide.
Knowing When Essbase uses Cell or Block Mode and Top-down or Bottom-up Mode
Formula on member Profit % will be executed in CELL and TOPDOWN mode.When Essbase determines that the formula will be executed in block and bottom-up mode, no message is written in the application log file.
Understanding Data Dependency Issues With Block Calculation Mode
Data dependency occurs if the accurate calculation of one or more members depends on another member or other on members being calculated previously. Most data dependency issues with block calculation mode occur when a formula contains IF ELSE or IF ELSEIF conditions. However, data dependencies can occur in other formulas; for example, when using the @PRIOR function.
Data Dependency Issues With IF ELSE and IF ELSEIF
When Essbase uses block calculation mode to calculate a formula that contains IF ELSE or IF ELSEIF conditions, it separates the members being calculated into two groups. The first group contains the members that satisfy the IF condition. The second group contains the members that satisfy the ELSE or ELSEIF conditions.
Essbase simultaneously calculates the members in the first group before simultaneously calculating the members in the second group. See Example 1.
If a formula contains data dependencies, ensure that the following conditions are met:
- Members on which the accurate calculation of other members depends are in the first group.
- Dependent members are in the second group.
If an IF condition has multiple ELSEIF conditions, Essbase evaluates each ELSEIF condition, placing the members that satisfy the ELSEIF condition in the first group and the members that satisfy subsequent ELSEIF or ELSE conditions in the second group. See Example 2.
Understanding Other Data Dependency Issues
Data dependencies can occur in formulas that do not contain IF ELSE conditions. See Example 3 for an example of data dependency in a formula containing the @PRIOR function.
You can also set CALCMODE BLOCK or CALCMODE BOTTOMUP at the server, application, or database level using the configuration setting CALCMODE.
Example 1, Example 2, and Example 3 illustrate use of the BLOCK and CELL options of the @CALCMODE function. Example 4 illustrates use of the BOTTOMUP and TOPDOWN options.
Consider a database with two dense dimensions, Time and Accounts. The following formula is placed on the Budget Sales member of the Accounts dimension. Because this is a formula containing @ISMBR applied to a dense member (Budget Sales), by default Essbase uses cell calculation mode. Use the @CALCMODE(BLOCK) function to specify block calculation mode for this formula.
@CALCMODE(BLOCK); IF(@ISMBR(Feb)) "Budget Sales"=100; ELSE "Budget Sales"=Feb+10;
According to the above formula, we expect that if the member being calculated is Feb, the Budget Sales value is 100. If the member being calculated is not Feb, the Budget Sales value is 100+10 (the value for Feb + 10).
Assume that we load the values 10, 20, and 30 into the Budget Sales data block for Jan, Feb and Mar, as follows:
Jan | Feb | Mar | |
Budget Sales | 10 | 20 | 30 |
Using block calculation mode, Essbase calculates the members satisfying the IF condition first. In this example, Feb is the only member that satisfies the IF condition. After calculating Feb, Essbase calculates the members Jan and Mar. In this example, the results are as expected:
Jan | Feb | Mar | |
Budget Sales | 110 | 100 | 110 |
Now consider the same database as in Example 1, but we place the following formula on the Budget Sales member of the Accounts dimension. As in Example 1, because this is a formula containing @ISMBR applied to a dense dimension member (Budget Sales), by default Essbase uses cell calculation mode. However, we use the @CALCMODE(BLOCK) function to specify the block calculation mode for this formula.
@CALCMODE(BLOCK); IF(@ISMBR(Mar)) "Budget Sales"=Feb+20; ELSEIF(@ISMBR(Jan)) "Budget Sales"=Feb+10; ELSE "Budget Sales"=100;
According to this formula, we want the Jan and Mar Budget Sales values to be calculated based on the Feb Budget Sales value, which is 100. We want to see the following results:
Jan | Feb | Mar | |
Budget Sales | 110 | 100 | 120 |
Assume that we load the values 10, 20, and 30 into the Budget Sales data block for Jan, Feb, and Mar, as follows:
Jan | Feb | Mar | |
Budget Sales | 10 | 20 | 30 |
Using block calculation mode, Essbase calculates the members satisfying the IF condition first, followed by the members satisfying the ELSEIF condition, followed by the members satisfying the ELSE condition. In this example, Hyperion Essbase calculates the members in the following order: Mar, Jan, Feb. The results are not what we want, because the calculation of Jan and Mar is dependent on the calculation of Feb and Feb is calculated after Jan and Mar. The inaccurate results are as follows:
Jan | Feb | Mar | |
Budget Sales | 30 | 100 | 40 |
To achieve the desired results, use the @CALCMODE(CELL) function.
The following formula calculates the members Opening Inventory and Ending Inventory using the @PRIOR function. There is a data dependency between Opening Inventory and Ending Inventory. The formula is placed on the Opening Inventory member. The example shows the results for January, February, and March.
@CALCMODE(BLOCK) "Opening Inventory"=@PRIOR("Ending Inventory")+10; "Ending Inventory"="Opening Inventory";
Before the calculation, there is no data for these members (the data is #MISSING or #MI):
Jan | Feb | Mar | |
Opening Inventory | #MI | #MI | #MI |
Ending Inventory | #MI | #MI | #MI |
Using block calculation mode, Essbase calculates the members simultaneously, taking the previous month's Ending Inventory #MISSING value as 0 for all member combinations and adding 10. This is not the desired result.
Jan | Feb | Mar | |
Opening Inventory | 10 | 10 | 10 |
Ending Inventory | 10 | 10 | 10 |
The following formula on the Opening Inventory member causes Essbase to use cell calculation mode (the default for formulas containing the @PRIOR function):
"Opening Inventory"=@PRIOR("Ending Inventory")+10; "Ending Inventory"="Opening Inventory";
The results are as follows:
Jan | Feb | Mar | |
Opening Inventory | 10 | 20 | 30 |
Ending Inventory | 10 | 20 | 30 |
Depending on the formula and the structure of the data, calculating a formula top-down versus bottom-up may involve two issues: performance (reflecting the number of calculations that must be made) and accuracy. This example compares calculation results to illustrate both of these issues.
Before the calculation, assume that Actual and Budget are members of a sparse dimension and they contain the following data:
Cola | New York | Sales | ||
Actual | Budget | |||
Jan | #MISSING | 50 | ||
Feb | 200 | #MISSING | ||
Mar | 400 | 450 |
The following formula is calculated bottom-up.
@CALCMODE(BOTTOMUP); Budget=Actual*1.10;
This bottom-up calculation calculates two values, based on existing combinations of Budget, with the following results:
Cola | New York | Sales | (Comment) | |||
Actual | Budget | |||||
Jan | #MISSING | #MISSING | (#MISSING*1.10) | |||
Feb | 200 | #MISSING | (No calculation is performed) | |||
Mar | 400 | 440 | (400*1.10) |
The following formula is calculated top-down.
@CALCMODE(TOPDOWN); Budget=Actual*1.10;
This top-down calculation calculates three values, considering all potential combinations of Budget, with the following results:
Cola | New York | Sales | (Comment) | |||
Actual | Budget | |||||
Jan | #MISSING | #MISSING | (#MISSING*1.10) | |||
Feb | 200 | 220 | (200*1.10) | |||
Mar | 400 | 440 | (400*1.10 |
@WITHATTR
CALCMODE configuration setting
The @CHILDREN () function returns all children of the specified member, excluding the specified member. This member set function can be used as a parameter of another function, where that parameter is a list of members.
@CHILDREN(mbrName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
@CHILDREN(Market)
returns East, West, South, and Central (in that order).
@CHILDREN(Margin)
returns Sales and COGS (in that order).
@ICHILDREN, which includes the specified member.
@ISCHILD
@ANCESTORS
@DESCENDANTS
@SIBLINGS
The @COMPOUND() function compiles the proceeds of a compound interest calculation. The calculation is based on the balances of the specified member at the specified rate across the specified range.
@COMPOUND(balanceMbr, rateMbrConst [, rangeList])
balanceMbr | Single member specification representing the beginning balance across a range of periods. The input can be either one deposit or a series of deposits. If balanceMbr is a constant, then Essbase assumes balanceMbr to be a single deposit in the first member of rangeList. This is equivalent to entering the constant value in the first member in the rangeList followed by zeros. The function keeps track of each deposit separately, but returns a composite value. If balanceMbr is a member, or a range, then it is assumed to be a series of deposits. |
rateMbrConst | Single member specification, variable name, or numeric expression in decimal form. This represents the interest rate per time period specified in the rangeList. If your interest is compounded monthly, this value would be the annual interest rate divided by 12. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the dimension tagged as Time. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. rangeList represents the range over which the interest is compounded. The last value in the range is the total compounded interest for that range. |
The following example determines the compound interest of a series of deposits, based on a credit rate of 0.0525, across a series of fiscal years:
"Compound Interest"=@COMPOUND(Deposit,"Credit Rate",FY1991:FY1994,FY1995);
This example produces the following report:
FY1991 FY1992 FY1993 FY1994 FY1995 ====== ====== ====== ====== ====== Credit Rate 0.0525 0.0525 0.0525 0.0525 0.0525 Compound Interest 0 105 110.5125 273.8144 288.1897 Deposit 0 2,000 0 3,000 0
The @COMPOUNDGROWTH() function calculates a series of values that represent a compound growth of the first nonzero value in the specified member across the specified range of members. The growth factor is calculated by multiplying the growth rate in the current time period by the previous period's result, yielding a compounded value. You can change the growth rate from period to period by placing a nonzero value in the current period's rateMbrConst cell.
principalMbr | Member specification representing the initial value to be compounded. The input line must be a single deposit. |
rateMbrConst | Single member specification, variable name, or expression which provides a constant value. This value can change across rangeList, making the new value be the new compound rate. If the value in the current period is zero, the compound rate is equal to zero, and the principal does not change. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the dimension tagged as Time. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
The following example determines the compound growth of Principal Amount based on Growth Rate across a series of fiscal years.
"Compound Growth"=@COMPOUNDGROWTH("Principal Amount",
"Growth Rate",FY1991:FY1996);
This example produces the following report:
FY1991 FY1992 FY1993 FY1994 FY1995 FY1996 ====== ====== ====== ====== ====== ====== Principal Amount 2,000 2,000 2,000 3,000 2,500 -500 Growth Rate 0.0525 0 0 0 0 0 Compound Growth 2,105 2,105 2,105 2,105 2,105 2,105
The @CONCATENATE function returns a character string that is the result of appending one character string to another character string.
@CONCATENATE (String1, String2)
String1 | A string or member name, or a function that returns a string or single member name |
String2 | A string or member name, or a function that returns a string or single member name |
This function creates a single character string by attaching one character string (String2) to the end of another character string (String1). The resulting string contains the characters from both source strings. The @CONCATENATE function can be nested to concatenate more than two strings (See Example 2).
The following examples are based on the Sample Basic database:
Example 1
The following function statement puts the string Product in front of the name of the member currently being processed in the Measures dimension; for example, if the current member being calculated is 100-10, the result is Product100-10:
@CONCATENATE("Product",@CURRMBR(Product))
Example 2
To concatenate more than two strings, you can nest multiple instances of the @CONCATENATE function. The following function statement returns string values starting with the current member of the Year dimension, followed by an underscore, followed by the current member of the Measures dimension; for example, if the current members being calculated are Qtr1 and Sales, the result is Qtr1_Sales:
@CONCATENATE(@CURRMBR(Year),@CONCATENATE("_",@CURRMBR(Measures)))
The @CORRELATION() function returns the correlation coefficient between two parallel data sets (expList1 and expList2). The correlation coefficient determines the relationship between two data sets.
@CORRELATION (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH,
expList1, expList2)
SKIPNONE | Includes all cells specified in expList1 and expList2, regardless of their content, during calculation of the correlation coefficient. |
SKIPMISSING | Excludes all #MISSING values from expList1 and
expList2 during calculation of the correlation coefficient. |
SKIPZERO | Excludes all zero (0) values from expList1 and expList2 during calculation of the correlation coefficient. |
SKIPBOTH | Excludes all zero (0) values and #MISSING values from
expList1 and expList2 during calculation of the correlation
coefficient. |
expList1 | The first list of member specifications, variable names, functions, or other numeric expressions. |
expList2 | The second list of member specifications, variable names, functions, or other numeric expressions. |
#MISSING
.#MISSING
if expList1
and expList2 (1) are empty, (2) contain only #MISSING
values, or (3) have a standard deviation of 0 (all values are constant).#MISSING
values
as zero (0) values, unless SKIPMISSING or SKIPBOTH is specified. If a
value in expList1 is #MISSING
, and SKIPMISSING is
specified, the value's corresponding value in expList2 is treated
as #MISSING
. (That is, both values are deleted before calculation.)
SKIPZERO and SKIPBOTH work similarly.The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Sales Correl. The calculation script calculates the correlation coefficient for a set of members (Sales for the children of Qtr1 and Qtr2). Because the calculation script fixes on Jun, the results are placed in Sales Correl->Jun.
This example uses the @RANGE function to generate expList1 and expList2:
FIX(June) "Sales Correl"=@CORRELATION(SKIPNONE, @RANGE(Sales,@CHILDREN(Qtr1)),@RANGE(Sales,@CHILDREN(Qtr2))); ENDFIX
This example produces the following report:
Colas Actual New York Sales Sales Correl ===== ============ Jan 678 #MI Feb 645 #MI Mar 675 #MI Apr 712 #MI May 756 #MI Jun 890 0.200368468
The @COUNT() function returns the number of data values in the specified data set (expList).
@COUNT (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)
SKIPNONE | Includes all cells specified in expList, regardless of their content, during calculation of the count. |
SKIPMISSING | Excludes all #MISSING values from expList during
calculation of the count. |
SKIPZERO | Excludes all zero (0) values from expList during calculation of the count. |
SKIPBOTH | Excludes all zero (0) values and #MISSING values from
expList during calculation of the count. |
expList | Comma-delimited list of member specifications, variable names, functions, or numeric expressions. |
The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Prod Count. This example calculates the count of all products for which a data value exists and uses the @RANGE function to generate expList:
FIX(Product) "Prod Count" = @COUNT(SKIPMISSING,@RANGE(Sales,@CHILDREN(Product))); ENDFIX
This example produces the following report. Since SKIPMISSING is specified
in the calculation script, the #MI
values for Diet Drinks are
skipped during the product count.
Jan New York Actual Budget ====== ====== Sales Colas 678 640 Root Beer 551 530 Cream Soda 663 510 Fruit Soda 587 620 Diet Drinks #MI #MI Product 2479 2300 Prod Count Product 4 4
The @CURGEN() function returns the generation number of the current member combination for the specified dimension. This number represents the number of members separating the current member from the top-most member of the dimension.
@CURGEN(dimName)
dimName | Single dimension name specification. dimName must be the name of the top-most member of the dimension. It cannot be another member name from within the dimension. |
Given the following database structure:
Year Qtr1 Jan, Feb, Mar Qtr2 Apr, May, Jun Qtr3 Jul, Aug, Sep Qtr4 Oct, Nov, Dec
@CURGEN provides the following results for the members shown:
Formula Current Member Value Position = @CURGEN(Year); Year 1 Position = @CURGEN(Year); Qtr2 2 Position = @CURGEN(Year); Oct 3
The @CURLEV() function returns the level number of the current member combination for the specified dimension. This number represents the number of members that separates the current member from its bottom-most descendant.
@CURLEV(dimName)
dimName | Single dimension name specification. dimName must be the name of the top-most member of the dimension. It cannot be another member name from within the dimension. |
Given the following database structure:
Year Qtr1 Jan, Feb, Mar Qtr2 Apr, May, Jun Qtr3 Jul, Aug, Sep Qtr4 Oct, Nov, Dec
@CURLEV provides the following results for the members shown:
Formula Current Member Value Position = @CURLEV(Year); Year 2 Position = @CURLEV(Year); Qtr3 1 Position = @CURLEV(Year); Aug 0
The @CURRMBR() function returns the member that is currently being calculated in the specified dimension (dimName). This function can be used as a parameter of another function, where that parameter is a single member or a list of members.
@CURRMBR(dimName)
dimName | A single dimension name specification. |
In the Sample Basic database,
@CURRMBR(Year);
returns Jan if the current member of Year being calculated is Jan.
As a more complex example, consider the following formula in the context of the Sample Basic database. Assume that the Measures dimension contains an additional member, Average Sales.
"Average Sales" (IF(@ISLEV(Product,0)) Sales; ELSE @AVGRANGE(SKIPNONE,Sales,@CHILDREN(@CURRMBR(Product))); ENDIF;);
This formula populates each upper-level member of the Product dimension (100, 200) at Average Sales. To calculate Average Sales, the Sales values for the level 0 members of Product are averaged and placed in their respective parent members. The Average Sales values for the level 0 Product members are the same as the Sales values, as specified by the IF statement in the calculation script.
This example produces the following report:
Jan New York Actual Sales Average Sales ===== ============= 100-10 5 5 100-20 10 10 100-30 15 15 100 30 10 200-10 20 20 200-20 25 25 200-30 30 30 200-40 35 35 200 110 27.5 300 #MI #MI 400 #MI #MI Diet 35 11.67 Product 140 35
The @CURRMBRRANGE() function generates a member list that is based on the relative position of the current member being calculated.
@CURRMBRRANGE(dimName, {GEN|LEV}, genLevNum, [startOffset], [endOffset])
dimName | Name of the dimension for which you want to return the range list. |
GEN|LEV | Defines whether the range list to be returned is based on a generation or a level within the dimension. |
genLevNum | Integer value that defines the absolute generation or level number of the range list to be returned. |
startOffset | Defines the first member in the range to be returned.
|
endOffset | Defines the last member in the range to be returned.
|
@CURRMBRRANGE(Year,LEV,0,-1,1)In this example, the full range list contains the level 0 members of the Year dimension (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec). If the current member being calculated in the Year dimension is Jan, the startOffset and endOffset parameters reduce this list to (Jan, Feb). Since there is no member prior to Jan in the full range list, only two members are returned: Jan itself and the member after it, Feb. If the current member being calculated is Feb, the subset list would include three members: Jan, Feb, Mar.
Average Inventory is calculated by summing opening inventories from the first month of the year to the current period plus one period, and dividing the result by the number of periods to date plus one period. This calculation is accomplished by defining the @CURRMBRRANGE function within the rangeList parameter of the @AVGRANGE function.
"Average Inventory" = @AVGRANGE(SKIPNONE,"Opening Inventory",
@CURRMBRRANGE(Year, LEV, 0, , 1));
This example produces the following result:
Jan Feb Mar Apr Nov Dec Opening Inventory 100 110 120 130 . . . 200 210 Average Inventory 105 110 115 120 . . ..155 155
Since a null value is specified for startOffset, the average operations always begin at the first member of the range list, Jan. The endOffset parameter, 1, specifies that the member after the current member being calculated is included in each average operation. So, for Average Inventory->Jan, the values for Jan and Feb are averaged; for <Average Inventory->Feb, the values for Jan, Feb, and Mar are averaged; and so on. The values for Nov and Dec are the same since there is no member after Dec in the range list.
Example 2
Inventory Turnover is calculated by summing period-to-date Sales and dividing the result by the Average Inventory.
Turnover = @SUMRANGE(Sales,@CURRMBRRANGE(Year, LEV, 0, , 0))/"Average Inventory"
which produces the following result:
Jan Feb Mar Apr Average Inventory 110 116.7 122.5 126 Sales 40 44 48 52 Turnover 0.36 0.72 1.08 1.46
The @DECLINE() function calculates the depreciation of an asset for the specified period using the declining balance method. The factor by which the declining balance depreciates the assets is specified using factorMbrConst. For example, to calculate a double declining balance, set factorMbrConst to 2.
@DECLINE (costMbr, salvageMbrConst, lifeMbrConst,
factorMbrConst [, rangeList])
costMbr | Single member specification representing the starting values of the assets. More than one asset can be input and depreciated across the specified range. The function calculates each asset separately. |
salvageMbrConst | Single member specification, variable name, or numeric expression that provides a constant value. This value represents the value of the asset at the end of the depreciation. |
lifeMbrConst | Single member specification, variable name, or numeric expression that provides a constant value. The value represents the number of periods over which the asset is depreciated. |
factorMbrConst | Single member specification, variable name, or numeric expression that provides a constant value. The value represents the factor by which the asset is depreciated. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the dimension tagged as Time. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. The range represents the periods over which the function is calculated. More than one asset can be depreciated. |
The following example calculates the depreciation of Asset for the specified series of fiscal years.
"Decline Dep" = @DECLINE(Asset,Residual,Life,2,FY1991:FY1992,FY1993,FY1994);
This example produces the following report:
FY1991 FY1992 FY1993 FY1994 ====== ====== ====== ====== Asset 9,000 0 0 0 Residual 750 0 0 0 Life 5 0 0 0 Decline Dep 3,600 2,160 1,296 778
The @DESCENDANTS function returns all descendants of the specified member, or those down to the specified generation or level. This function excludes the specified member. You can use this member set function as a parameter of another function, where that parameter is a list of members.
@DESCENDANTS(mbrName [, genLevNum| genLevName])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
genLevNum | Optional. An integer value that defines the absolute generation or level number down to which to select the members. A positive integer defines a generation number. A value of 0 or a negative integer defines a level number. |
genLevName | Optional. Level name or generation name down to which to select the members. |
@DESCENDANTS(100)
,
Essbase returns 100-10, 100-20, 100-30 (in that order). This order is
important to consider when you use the @DESCENDANTS member set
function with certain forecasting and statistical functions.In the Sample Basic database:
@DESCENDANTS(East)
returns New York, Massachusetts, Florida, Connecticut, and New Hampshire (in that order).
@DESCENDANTS(Profit)
returns Margin, Sales, COGS, Total Expenses, Marketing, Payroll, and Misc (in that order).
@DESCENDANTS(Market,2)
returns East, West, South, and Central (in that order).
@DESCENDANTS(Diet,0)
returns 100-20, 200-20, and 300-30 (in that order).
@IDESCENDANTS
@RDESCENDANTS
@IRDESCENDANTS
@ISDESC
@ANCESTORS
@CHILDREN
@SIBLINGS
The @DISCOUNT() function calculates a value discounted by the specified rate, from the first period of the range to the period in which the amount to discount is found. The answer is returned in the same period. More than one value can be discounted simultaneously in this manner.
@DISCOUNT (cashMbr, rateMbrConst [, rangeList])
cashMbr | Member specification representing the value you want to discount from the last period in rangeList to the current period. |
rateMbrConst | Member specification, variable name, or numeric expression which provides a constant value. The value represents the rate per period which cashMbr is discounted. It is a decimal value, not a percent. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
The following example discounts the values in Cash by the rates in Credit Rate and places the results in Discount Amount for each fiscal year.
"Discount Amount" = @DISCOUNT(Cash,"Credit Rate",FY1991:FY1994,FY1995);
This example produces the following report:
FY1991 FY1992 FY1993 FY1994 FY1995 ====== ====== ====== ====== ====== Cash 0.00 0.00 1000.00 1000.00 0.00 Credit Rate 0.00 0.00 0.05 0.05 0.00 Discount Amount #MI #MI 863.84 822.70 #MI
The @EXP() function returns the exponent of a specified expression; that is, the value of e (the base of natural logarithms) raised to the power of the specified expression.
@EXP (expression)
expression | Single member specification, variable name, function, or other numeric
expression. If less than -700 or greater than 700, Essbase returns #MISSING .
|
The following example is based on a variation of Sample Basic:
Index = @EXP("Variance %"/100);
This example produces the following result:
East West South Central Variance % 10.7 10.9 3.6 3.6 Index 1.11293 1.11516 1.03666 1.03666
The @FACTORIAL() function returns the factorial of expression. The factorial of a number is equal to 1*2*3*...* number.
@FACTORIAL(expression)
expression | Single member specification or numeric expression. |
#MISSING
. #MISSING
.
@FACTORIAL(1) 1 @FACTORIAL(5) 120
The @GEN() function returns the generation number of the specified member.
@GEN(mbrName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
@GEN(Year) 1 @GEN(Qtr3) 2
The @GENMBRS() function returns all members with the specified generation number or generation name in the specified dimension.
@GENMBRS(dimName,genName|genNum)
dimName | A single dimension name specification. |
genName|genNum | Generation name or generation number from dimName. A positive integer defines a generation number. |
@GENMBRS(Product,2)
,
Essbase returns 100, 200, 300, 400, Diet (in that order). This order is
important to consider when you use the @GENMBRS member set function
with certain forecasting and statistical functions. In the Sample Basic database:
@GENMBRS(Year,Month) @GENMBRS(Year,3)
both return the following members since generation 3 of the Year dimension is named Month:
Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, and Dec (in that order).The following example restricts the calculation to members with the combination Budget and one of the members of the Market dimension with a generation name of State.
FIX(Budget,@GENMBRS(Market,State)) CALC DIM (Year,Measures); ENDFIX
The @GROWTH() function calculates a series of values that represent a linear growth of the first nonzero value encountered in principalMbr across the specified rangeList. Growth is calculated by multiplying the growth rate in rateMbrConst by the original principalMbr. This value is then added to the previous time period's result, yielding the new value.
@GROWTH(principalMbr, rateMbrConst [, rangeList])
principalMbr | Single member specification that represents the initial value of the value to grow. The first nonzero value encountered is the initial value. Other principalMbr values after the first are ignored. |
rateMbrConst | Single member specification, variable name, or numeric expression providing a constant value that represents the decimal growth rate to be applied (for example, 10% = .1). |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
The following example calculates the growth of Principal Amount, using the rate found in Growth Rate for each fiscal year. The results are placed in Growth Amount.
"Growth Amount"=@GROWTH("Principal Amount","Growth Rate",FY1991:FY1996);
This example produces the following report:
FY1991 FY1992 FY1993 FY1994 FY1995 FY1996 ====== ====== ====== ====== ====== ====== Principal Amount 1,000 0 2,000 0 0 0 Growth Amount 1,050 1,120 1,200 1,280 1,380 1,480 Growth Rate 0.05 0.07 0.08 0.08 0.1 0.1
The @IALLANCESTORS() function returns the specified member and all the ancestors of that member, including ancestors of any occurrences of the specified member as a shared member. You can use this member set function as a parameter of another function, where that parameter is a list of members.
@IALLANCESTORS(mbrName)
mbrName | A valid single member name or member combination, or a function that returns a single member or member combination. |
The following example is based on the Sample Basic database. Sample Basic has a shared level of diet drinks, which includes 100-20 (Diet Cola). So 100-20 (Diet Cola) is a descendant of 100 (Colas) and is a shared member descendant of >Diet:
100 100-10 100-20 Diet 100-20 (Shared Member)
The following calculation script increases by 5% the Budget Sales values of 100-20 and all its ancestors, including Diet:
FIX(Budget,@IALLANCESTORS("100-20")) Sales = Sales * 1.05; ENDFIX
This example produces the following report. This report shows that the Budget->Sales values for 100-20, 100, Diet, and Product (100-20 and its ancestors) have been increased by 5%. The original values were 2610, 8980, 8260, and 28480, respectively.
Jan Actual Budget Sales Sales ===== ===== Market 100-10 4860 5200 100-20 2372 2740.5 * 100-30 1082 1170 100 8314 9429 * 100-20 2372 2610 200-20 3122 3090 300-30 2960 2560 Diet 8454 8673 * Product 31538 30954 *
@ALLANCESTORS, which excludes the specified
member.
@IANCESTORS
The @IANCESTORS() function returns the specified member and either (1) all ancestors of the specified member or (2) all ancestors up to the specified generation or level. You can use this member set function as a parameter of another function, where that parameter is a list of members.
@IANCESTORS (mbrName [, genLevNum | genLevName])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
genLevNum | Optional. An integer value that defines the absolute generation or level number up to which to select the members. A positive integer defines a generation number. A value of 0 or a negative integer defines a level number. |
genLevName | Optional. Level name or generation name up to which to select the members. |
@IANCESTORS(200-30)
,
Essbase returns 200-30, 200, Product (in that order). This order is important
to consider when you use the @IANCESTORS member set function with
certain forecasting and statistical functions. In the Sample Basic database:
@IANCESTORS("New York")
returns New York, East, Market (in that order).
@IANCESTORS(Qtr4)
returns Qtr4, Year (in that order).
@IANCESTORS(Sales,-2)
returns Sales, Margin, Profit (in that order). Members higher than level 2 are not returned, as specified in the formula.
@IANCESTORS("100-10",1)
returns 100-10, 100, Product (in that order). All ancestors are returned (up to generation 1), as specified in the formula.
@ANCESTORS, which excludes the specified member.
@IALLANCESTORS
The @ICHILDREN() function returns the specified member and all of its children. This member set function can be used as a parameter of another function, where that parameter is a list of members.
@ICHILDREN(mbrName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
@ICHILDREN(Market)
returns Market, East, West, South, and Central (in that order).
@ICHILDREN(Margin)
returns Margin, Sales, and COGS (in that order).
@CHILDREN, which excludes the specified member.
The @IDESCENDANTS function returns the specified member and either (1) all descendants of the specified member or (2) all descendants down to a specified generation or level. You can use this member set function as a parameter of another function, where that parameter is a list of members.
@IDESCENDANTS(mbrName[, genLevNum | genLevName])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
genLevNum | Optional. An integer value that defines the absolute generation or level number down to which to select the members. A positive integer defines a generation number. A value of 0 or a negative integer defines a level number. |
genLevName | Optional. Level name or generation name down to which to select the members. |
@IDESCENDANTS(100)
,
Essbase returns the members: 100, 100-10, 100-20, 100-30 (in that order).
This order is important to consider when you use the @IDESCENDANTS
member set function with certain forecasting and statistical functions.In the Sample Basic database:
@IDESCENDANTS(East)
returns East, New York, Massachusetts, Florida, Connecticut, and New Hampshire (in that order).
@IDESCENDANTS(Profit)
returns Profit, Margin, Sales, COGS, Total Expenses, Marketing, Payroll, and Misc (in that order).
@IDESCENDANTS(Market,2)
returns Market, East, West, South, and Central (in that order).
@IDESCENDANTS(South,-1)
returns South.
@DESCENDANTS
@IRDESCENDANTS
@RDESCENDANTS
@ISDESC
@ANCESTORS
@CHILDREN
@SIBLINGS
The @ILSIBLINGS() function returns the specified member and all of the left siblings of the member. Left siblings are children that share the same parent as the member and that precede the member in the database outline.
This member set function can be used as a parameter of another function, where that parameter is a list of members.
@ILSIBLINGS(mbrName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
@ILSIBLINGS(Florida)
returns New York, Massachusetts, and Florida (in that order). New York and Massachusetts appear above Florida in the Sample Basic outline.
@ILSIBLINGS(Qtr3)
returns Qtr1, Qtr2, and Qtr3 (in that order). Qtr1 and Qtr2 appear above Qtr3 in the Sample Basic outline.
@LSIBLINGS, which excludes the specified member.
The @INT() function returns the next lowest integer value of expression.
@INT (expression)
expression | Member specification or mathematical expression that generates a numeric value. |
The following example is based on the Sample Basic database. Assume that the Profit % member is not tagged as Dynamic Calc.
The following formula rounds the values for West down to the nearest integer.
West=@INT(@SUM(@CHILDREN(West)));
This example produces the following report:
Profit % Cola Actual Jan Feb Mar === === === California 38.64 37.98 38.37 Oregon 17.50 16.13 16.11 Washington 29.23 30.90 32.00 Utah 23.08 23.08 20.97 Nevada -3.95 -6.76 -5.33 West 104 101 102
@ABS
@REMAINDER
@ROUND
@TRUNCATE
The @INTEREST() function calculates the simple interest in balanceMbr at the rate specified by creditrateMbrConst if the value specified by balanceMbr is positive, or at the rate specified by borrowrateMbrConst if balanceMbr is negative. The interest is calculated for each time period specified by rangeList.
@INTEREST(balanceMbr, creditrateMbrConst, borrowrateMbrConst
[, rangeList])
balanceMbr | Single member specification representing the balance at the time the interest is calculated. |
creditrateMbrConst | Single member specification, variable name, or numeric expression providing a constant value. The value must be a decimal number that corresponds to a percentage. The value represents the per-period interest rate. |
borrowrateMbrConst | Single member specification, variable name, or numeric expression providing a constant value. The value must be a decimal number corresponding to a percentage value. The value represents the per-period interest rate. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the dimension tagged as Time. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
This example calculates the interest for Balance, using Credit Rate for positive balances and using Borrow Rate for negative balances. The results are placed in Interest Amount for each fiscal year.
"Interest Amount" = @INTEREST(Balance,"Credit Rate","Borrow Rate",
FY1991:FY1994,FY1995,FY1996);
This example produces the following report:
FY1991 FY1992 FY1993 FY1994 FY1995 FY1996 ====== ====== ====== ====== ====== ====== Balance 2000.00 3000.00 -1000.00 3000.00 9000.00 -6000.00 Credit Rate 0.065 0.065 0.065 0.065 0.065 0.065 Borrow Rate 0.1125 0.1125 0.1125 0.1125 0.1125 0.1125 Interest Amount 130.00 195.00 -112.50 195.00 585.00 -675.00
The @IRDESCENDANTS function returns the specified member and either (1) all descendants of the specified member or (2) all descendants down to an optionally specified generation or level. If one or more of the descendants are shared, the result also includes either all descendants of each member being shared or descendants down to the specified generation or level.
You can use this member set function as a parameter of another function, where that parameter is a list of members. In the absence of shared members, @IRDESCENDANTS and @IDESCENDANTS have identical behavior.
@IRDESCENDANTS(mbrName[, genLevNum | genLevName])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination |
genLevNum | Optional. An integer value that defines the absolute generation or level number down to which to select the members. A positive integer defines a generation number. A value of 0 or a negative integer defines a level number. |
genLevName | Optional. Level name or generation name down to which to select the members. |
Assume a variation of the Sample Basic database such that the Product dimension includes the following members:
Product 100 100-10 100-20 100-30 200 200-10 200-20 200-30 200-40 Diet 100 (Shared Member) 200 (Shared Member)
Diet has two children "100" and "200" instead of "100-10", "200-20" and "300-30". The members "100" and "200" are shared members.
@IRDESCENDANTS(Diet)
returns the members: Diet, 100, 100-10, 100-20, 100-30, 200, 200-10, 200-20, 200-30, 200-40 (in that order).
Example 2
@IRDESCENDANTS(East)
returns East, New York, Massachusetts, Florida, Connecticut, and New Hampshire (in that order) and is exactly the same as @IDESCENDANTS(East).
@RDESCENDANTS
@IDESCENDANTS
@DESCENDANTS
@ISDESC
@ICHILDREN
@ISIBLINGS
@IANCESTORS
The @IRR() function calculates the Internal Rate of Return on a cash flow that must contain at least one investment (negative) and one income (positive) value.
@IRR(cashflowMbr, discountFlag[, rangeList])
cashflowMbr | Single member specification. |
discountFlag | Member specification, variable name, or numeric expression providing a constant value of either 1 or 0. discountFlag indicates whether the function should discount from the first period. 1 means do not discount from the first period. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
This example calculates the Internal Rate of Return (Return) on a cash flow (Cash).
Return = @IRR(Cash,0,FY1990:FY1994,FY1995:FY2000);
This example produces the following report:
FY1990 FY1991 FY1992 FY1993 FY1994 FY1995 ====== ====== ====== ====== ====== ====== Cash (1,000) 500 600 500 #MISSING #MISSING Rate 0 0 0 0 #MISSING #MISSING Return 0 0 0 0 0 0
The @IRSIBLINGS() function returns the specified member and all of the right siblings of the specified member. Right siblings are children that share the same parent as the member and that follow the member in the database outline.
This member set function can be used as a parameter of another function, where that parameter is a list of members.
@IRSIBLINGS(mbrName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
@IRSIBLINGS(Florida)
returns Florida, Connecticut, and New Hampshire (in that order). Connecticut and New Hampshire appear below Florida in the Sample Basic outline.
@IRSIBLINGS(Qtr3)
returns Qtr3 and Qtr4 (in that order). Qtr4 appears below Qtr3 in the Sample Basic outline.
@RSIBLINGS, which excludes mbrName from the list.
The @ISACCTYPE() function returns TRUE if the current member has the associated accounts tag.
@ISACCTYPE(tag)
tag | Valid account tag defined in the current database. Any of the following tags may be used: First, Last, Average, Expense, and Two-Pass. |
The following example is based on the Sample Basic database. For members with the Expense accounts tag, the formula uses the @ABS function to calculate Budget as the absolute value of Budget.
IF (@ISACCTYPE(Expense)) Budget = @ABS(Budget); ENDIF;
The @ISANCEST() function returns TRUE if the current member is an ancestor of the specified member. This function excludes the specified member.
@ISANCEST(mbrName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
@ISANCEST(California) TRUE for Market, West @ISANCEST(West) FALSE for California, West, East
@ISIANCEST, which includes the specified member, returning TRUE if the current member is the specified member or the parent of the specified member.
The @ISCHILD() function returns TRUE if the current member is a child of the specified member. This function excludes the specified member.
@ISCHILD(mbrName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
@ISCHILD(East) TRUE for New York, Florida, Connecticut @ISCHILD(Margin) FALSE for Measures, Profit, Margin
@ISICHILD, which includes the specified member, returning TRUE if the current member is the specified member or the parent of the specified member.
The @ISDESC() function returns TRUE if the current member is a descendant of the specified member. This function excludes the specified member.
@ISDESC(mbrName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
@ISDESC(Market) TRUE for West, California, Oregon, Washington, Utah, Nevada @ISDESC(Profit) FALSE for Measures, Profit, Profit %
@ISIDESC, which includes the specified member, returning TRUE if the current member is the specified member or the parent of the specified member.
The @ISGEN() function returns TRUE if the current member of the specified dimension is in the specified generation.
@ISGEN(dimName, genName | genNum)
dimName | The name of a dimension. |
genName | genNum | Generation name specification, or a non-negative number that defines the number of a generation. |
In the Sample Basic database:
@ISGEN(Measures,3)
returns TRUE if the current member is Margin, Total Inventory, or Margin %, because these members are all in generation 3 of the Measures dimension.
@ISGEN(Market,2)
returns FALSE if the current member is New York or Market, because these members are not in generation 2 of the Market dimension.
The @ISIANCEST() function returns TRUE if the current member is the specified member or an ancestor of the specified member. This function includes the specified member.
@ISIANCEST(mbrName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
@ISIANCEST(California)
returns TRUE for Market, West, and California. California is the specified member, and West and Market are ancestors of California.
@ISIANCEST(Qtr1)
returns FALSE for Jan, Feb, Mar, Qtr2. None of these members is the specified member (Qtr1) or an ancestor of Qtr1.
@ISANCEST, which excludes the specified member.
The @ISIBLINGS() function returns the specified member and all siblings of that member. This member set function can be used as a parameter of another function, where that parameter is a list of members.
@ISIBLINGS(mbrName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
Essbase sorts the generated list of members in ascending order.
Using Sample Basic as an example, if you specify 200-30 for mbrName, Essbase returns 200-10, 200-20, 200-30, 200-40 (in that order). This order is important to consider when you use the @ISIBLINGS member set function with certain forecasting and statistical functions.
In the Sample Basic database:
@ISIBLINGS(California)
returns California, Oregon, Washington, Utah, and Nevada (in that order), because these members are siblings of California.
@ISIBLINGS(Qtr2)
returns Qtr1, Qtr2, Qtr3, and Qtr4 (in that order), because these members are siblings of Qtr2.
@SIBLINGS, which excludes the specified member.
The @ISICHILD() function returns TRUE if the current member is the specified member or a child of the specified member.
@ISICHILD(mbrName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
@ISICHILD(South) TRUE for Texas, Oklahoma, Louisiana, New Mexico, South @ISICHILD(Profit) FALSE for Measures, Sales
@ISCHILD, which excludes the specified member.
The @ISIDESC() function returns TRUE if the current member is the specified member or a descendant of the specified member.
@ISIDESC(mbrName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
@ISIDESC(South) TRUE for Texas, Oklahoma, Louisiana, New Mexico, South @ISIDESC(West) FALSE for Market, East, South, and Central
@ISDESC, which excludes the specified member.
The @ISIPARENT() function returns TRUE if the current member is the specified member or the parent of the specified member.
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
@ISIPARENT(Qtr1) TRUE for Year, Qtr1 @ISIPARENT(Margin) FALSE for Measures, Sales
@ISPARENT, which excludes the specified member.
The @ISISIBLING() function returns TRUE if the current member is the specified member or a sibling of the specified member.
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
@ISISIBLING(Qtr2) TRUE for Qtr1, Qtr2, Qtr3, and Qtr4 @ISISIBLING(Actual) FALSE for Scenario
@ISSIBLING, which excludes the specified member.
The @ISLEV() function returns TRUE if the current member of the specified dimension is in the specified level.
dimName | Name of a dimension. |
levName | levNum | A level name or an integer value that defines the number of a level. A value of 0 or a negative integer defines a level number. |
In the Sample Basic database:
@ISLEV(Market,0) TRUE if the current member of Market is New York, California, Texas, or Illinois @ISLEV(Year,1) FALSE if the current member of Year is Jan, Feb, or Mar
The @ISMBR() function returns TRUE if the current member matches any one of the specified members.
@ISMBR(mbrName | rangeList | mbrList)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
rangeList | A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. |
mbrList | A comma-delimited list of members. |
If any term returns TRUE, the @ISMBR function returns TRUE.
In the Sample Basic database:
@ISMBR("New York":"New Hampshire") TRUE for Florida @ISMBR(@CHILDREN(Qtr1)) FALSE for Qtr2, Year
The @ISPARENT() function returns TRUE if the current member is the parent of the specified member. This function excludes the specified member.
@ISPARENT(mbrName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
@ISPARENT("New York") TRUE for East @ISPARENT(Profit) FALSE for Margin
@ISIPARENT, which includes the specified member, returning TRUE if the current member is the specified member or the parent of the specified member.
The @ISSAMEGEN() function returns TRUE if the current member is the same generation as the specified member.
@ISSAMEGEN(mbrName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
@ISSAMEGEN(West) TRUE for East @ISSAMEGEN(West) FALSE for California
The @ISSAMELEV() function returns TRUE if the current member is the same level as the specified member.
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
@ISSAMELEV(Sales) FALSE for Total Expenses @ISSAMELEV(Jan) TRUE for Apr, Jul, Oct
The @ISSIBLING() function returns TRUE if the current member is a sibling of the specified member. This function excludes the specified member.
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
@ISSIBLING("New York") TRUE for Florida, New Hampshire @ISSIBLING(Sales) FALSE for Margin
@ISISIBLING, which includes the specified member.
The @ISUDA() function returns TRUE if the specified user-defined attribute (UDA) exists for the current member of the specified dimension at the time of the calculation.
@ISUDA(dimName,UDAStr)
dimName | Dimension name specification that contains the member you are checking. |
UDAStr | user-defined attribute (UDA) name string. |
This example is based on the Sample Basic database. The Market dimension has members that indicate a geographic location. Some members represent major markets. The example below calculates the database and stores a budget amount for the upcoming year based on the actual amount from this year. A different sales growth rate is applied to major markets than to small markets.
FIX (Budget) Sales (IF(@ISUDA(Market,"Major Market")) Sales = Sales->Actual * 1.2; ELSE Sales = Sales->Actual * 1.1; ENDIF;); ENDFIX
This example tests to see if the current member of Market has a UDA called "Major Market". If it does, the Budget->Sales value is set to 120% of Actual->Sales. If it does not, the Budget->Sales value is set to 110% of Actual->Sales.
The @LEV() function returns the level number of the specified member.
@LEV(mbrName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
Function Level Returned @LEV(Margin) 1 @LEV("New York") 0
The @LEVMBRS() function returns all members with the specified level number or level name in the specified dimension.
@LEVMBRS(dimName, levName|levNum)
dimName | Dimension name specification. |
levName|levNum | A level name or an integer value that defines the number of a level. The integer value must be 0 or a positive integer. |
@LEVMBRS(Product,1)
,
Essbase returns 100, 200, 300, 400, Diet (in that order). This order is
important to consider when you use the @LEVMBRS
member set function
with certain forecasting and statistical functions.In the Sample Basic database:
@LEVMBRS(Measures,"Profit and Loss") @LEVMBRS(Measures,0)
both return the following members if level 0 of the Measures dimension is named Profit and Loss:
Sales, COGS, Marketing, Payroll, Misc, Opening Inventory, Additions, Ending Inventory, Margin %, Profit %, and Profit per Ounce (in that order).
@LEVMBRS(Scenario,0)
returns Actual, Budget, Variance, and Variance %.
The following example restricts the calculation to members with the combination Budget and one of the members of the Market dimension with a level name of "State".
FIX (Budget,@LEVMBRS(Market,State)) CALC DIM (Year,Measures); ENDFIX
The @LIST() function creates and distinguishes lists that are processed by functions that require list arguments. @LIST can be used to create expLists, member lists, or rangeLists. @LIST treats a collection of parameters as one entity.
@LIST(argument1, argument2, ..., argumentN)
argument1, argument2, ..., argumentN | The list of arguments that are collected and treated as one argument so they can be processed by the parent function. Arguments can be member names, member combinations, member set functions, range functions, and numeric expressions. |
The following example is based on the Sample Basic database. Assume that the Year dimension contains an additional member, Sales Correl. @LIST is used with the @CORRELATION function to determine the sales relationship between a product's two peak periods (Jan through Mar and Apr through May):
FIX(Sales) "Sales Correl" = @CORRELATION(SKIPNONE, @LIST(Jan,Feb,Mar),@LIST(Apr,May,Jun)); ENDFIX
This example produces the following report:
Colas Actual New York Sales ===== Jan 678 Feb 645 Mar 675 Apr 712 May 756 Jun 890 Sales Correl 0.200368468
The @LN() function returns the natural logarithm (base e) of the specified expression.
@LN (expression)
expression | Single member specification, member combination, or other numeric
expression. If less than or equal to 0, Essbase returns #MISSING . |
The following example is based on a variation of Sample Basic:
LN_Sales = @LN(Sales);
This example produces the following result:
Cola East
Jan Feb Mar Nov Dec
Sales 100 110 120 . . . 0 210
LN_Sales 4.65052 4.70048 4.78749 . . . #MISSING
5.34710
The @LOG() function returns the result of a logarithm calculation where you can specify both the base to use and the expression to calculate.
@LOG (expression [, base])
expression | Single member specification, variable name, function, or other numeric
expression. If less than or equal to 0, Essbase returns #MISSING .
|
base | Optional. Single member specification, member combination, or numeric
expression.
|
The @LOG function returns the logarithm of expression calculated using the specified base. @LOG (x,b) is equivalent to logb(x).
The following example is based on a variation of Sample Basic:
LOG2_Sales = @LOG(Sales,2);
This example produces the following result:
Cola East Jan Feb Mar Nov Dec Sales 100#MISSING
120 . . . 0 210 LOG2_Sales 6.64386#MISSING
6.90689 . . .#MISSING
7.71425
The @LOG10() function returns the base-10 logarithm of the specified expression.
@LOG10 (expression)
expression | Single member specification, variable name, function, or other numeric
expression. If less than or equal to 0, Essbase returns #MISSING . |
The following example is based on a variation of Sample Basic:
LOG10_Sales = @LOG10(Sales);
This example produces the following result:
Product Actual East West South Central Sales 87398 132931 50846 129680 LOG10_Sales 4.94150 5.12363 4.70626 5.11287
The @LSIBLINGS() function returns the left siblings of the specified member. Left siblings are children that share the same parent as the member and that precede the member in the database outline. This function excludes the specified member.
This member set function can be used as a parameter of another function, where that parameter is a list of members.
@LSIBLINGS(mbrName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
@LSIBLINGS(Qtr4)
returns Qtr3, Qtr2, and Qtr1 (in that order). These members appear above Qtr4 in the Sample Basic outline.
@LSIBLINGS(Utah)
returns Washington, Oregon, and California (in that order). These members appear above Utah in the Sample Basic outline.
@ILSIBLINGS, which includes the specified member.
@RSIBLINGS
The @MATCH function performs wildcard member selections.
@MATCH (mbrName|genName|levName, "pattern")
mbrName | The default or user-defined name of the member on which to base the search. Essbase searches the member names and alias names of the specified member and its descendants. |
genName | The default or user-defined name of the generation to search. Essbase searches all member names and member alias names in the generation. |
levName | The default or user-defined name of the level to search. Essbase searches all member names and member alias names in the level. |
"pattern" | The character pattern to search for, including a wildcard character
(* or ?).
? substitutes one occurrence of any character. You can use ? anywhere in the pattern. * substitutes any number of characters. You can use * only at the end of the pattern. To include spaces in the character pattern, enclose the pattern in double quotation marks (""). |
This function performs a trailing-wildcard member selection. Essbase searches for member names and alias names that match the pattern you specify and returns the member and alias names it finds.
If the members names in the database you are searching are case-sensitive, the search is case-sensitive. Otherwise, the search is not case-sensitive. To define database member names as case-sensitive, choose Settings | Case Sensitive Members from the Application Manager Outline Editor menu.
You can use more than one @MATCH function in a calculation script.
If Essbase does not find any members that match the chosen character pattern, it returns no member names and continues with the other calculation commands in the calculation script.
In the Sample Basic database:
@MATCH(Product,"???-10")
returns: 100-10, 200-10, 300-10, and 400-10
@MATCH(Year,"J*")
returns: Jan, Jun, Jul
@MATCH(Product,"C*")
returns: 100 (Colas), 100-10 (Cola), 100-30 (Caffeine Free Cola), 300 (Cream Soda)
The @MAX() function returns the maximum value among the results of the expressions in the specified member list.
@MAX(expList)
expList | Comma-delimited list of members, variable names, functions, and numeric expressions, all of which return numeric values. |
Depending on the values in the list, @MAX may return a zero(0)
or #MISSING
value. For full control over skipping or inclusion
of zero(0) and #MISSING
values, it is recommended to use the
@MAXS function instead of the @MAX function.
This example is based on the Sample Basic database:
Qtr1 = @MAX(Jan:Mar);
This example produces the following report:
Colas New York Actual Jan Feb Mar Qtr1 === === === ==== Sales 678 645 675 678
The @MAXRANGE() function returns the maximum value of the specified member across the specified range of members.
@MAXRANGE (mbrName [ ,rangeList])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
rangeList | A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
Depending on the values in the list, @MAXRANGE may return a zero(0)
or #MISSING
value. For full control over skipping or inclusion
of zero(0) and #MISSING
values, it is recommended to use the
@MAXSRANGE function instead of the @MAXRANGE
function.
In the Sample Basic database:
Qtr1 = @MAXRANGE(Sales,@CHILDREN(Qtr1));
This example produces the following report:
Colas New York Actual Jan Feb Mar Qtr1 === === === ==== Sales 678 645 675 678
The @MAXS() function returns the maximum value among the results
of the expressions in the specified member list. @MAXS enables skipping
of #MISSING
and 0 values, in contrast with the @MAX
function which cannot ignore these values.
@MAXS (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)
SKIPNONE | Includes all cells specified in expList in the operation, regardless of their content |
SKIPMISSING | Ignores all #MISSING values |
SKIPZERO | Ignores all 0 values |
SKIPBOTH | Ignores all 0 and #MISSING values |
expList | Comma-delimited list of members, variable names, functions, or numeric expressions, all of which return numeric values |
#MISSING
values are greater than negative data
values and less than positive data values, if the data being calculated
includes only negative and #MISSING
values, @MAXS
returns #MISSING
. #MISSING
values, @MAXS may return either #MISSING
or 0 values
in an unpredictable manner. #MISSING
values, the @MAXS function is more flexible and its use may be
preferred over using the @MAX function. For both examples, assume a database similar to Sample Basic. The Measures
dimension includes two members: COGS (cost of goods sold) and OtherInc_Exp
(miscellaneous income and expenses). The data can include 0 and #MISSING
values.
Qtr1_Max = @MAXS(SKIPBOTH, Jan:Mar);
This example ignores #MISSING
and 0 values for all members
of the Measures dimension. This example produces the following results:
Jan Feb Mar Qtr1_Max ======== ======== ======== ======== COGS #MISSING 1500 2300 2300 OtherInc_Exp -500 -350 0 -350
Qtr1_Max = @MAXS(SKIPNONE, Jan:Mar);
This example includes #MISSING
and 0 values in the calculation,
for all members of the Measures dimension. This example produces the following
results:
Jan Feb Mar Qtr1_Max ======== ======== ======== ======== COGS #MISSING 1500 2300 2300 OtherInc_Exp -500 -350 0 0
The @MAXSRANGE() function returns the maximum value of the specified
member across the specified range of members. @MAXSRANGE enables
skipping of #MISSING
and 0 values, in contrast with the @MAXRANGE
function, which cannot ignore these values.
@MAXSRANGE (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, mbrName [ ,rangeList])
SKIPNONE | Includes all cells specified in expList in the operation, regardless of their content |
SKIPMISSING | Ignores all #MISSING values |
SKIPZERO | Ignores all 0 values |
SKIPBOTH | Ignores all 0 and #MISSING values |
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination |
rangeList | A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
#MISSING
values are considered to be greater than negative
data values and less than positive data values. If the data being calculated
includes only negative and #MISSING
values, @MAXSRANGE
returns #MISSING
. For both examples, assume a database similar to Sample Basic. The Measures
dimension includes two members: COGS (cost of goods sold) and OtherInc_Exp
(miscellaneous income and expenses). The data can include 0 and #MISSING
values. For both members of the Measures dimension, the result is the same--the
maximum value for the OtherInc_Exp member across the specified range.
Qtr1_Max = @MAXSRANGE (SKIPBOTH, OtherInc_Exp, @CHILDREN(Qtr1));
This example ignores #MISSING
and 0 values and produces the
following results:
Jan Feb Mar Qtr1_Max ======== ======== ======== ======== OtherInc_Exp -500 #MISSING -250 -250 COGS 0 1500 2300 -250
Qtr1_Max = @MAXSRANGE (SKIPNONE, OtherInc_Exp, @CHILDREN(Qtr1));
Using the same data as Example 1, Example 2 demonstrates what happens
if you do not skip 0 and #MISSING
values in the data. Example
2 produces the following report:
Jan Feb Mar Qtr1_Max ======== ======== ======== ======== OtherInc_Exp -500 #MISSING -250 #MISSING COGS 0 1500 2300 #MISSING
The @MDALLOCATE() 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.
The @MDALLOCATE 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.
@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.
|
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:
|
round | Optional. One of the following:
|
numDigits | An integer that represents the number of decimal places to round to.
You must specify numDigits if you specify roundAmt.
|
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, Essbase discards rounding errors.
To specify roundErr, choose from one of the following:
|
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
The @MDANCESTVAL() function returns ancestor-level data from multiple dimensions based on the current member being calculated.
@MDANCESTVAL(dimCount, dimName1, genLevNum1. . . dimNameX, genLevNumX [,mbrName])
dimCount | Integer value that defines the number of dimensions from which ancestor values are being returned. |
dimName1, . . . dimNameX | Defines the dimension names from which the ancestor values are to be returned. You must specify a genLevNum for every dimName. |
genLevNum, . . . genLevNumX | Integer value that defines the absolute generation or level number from which the ancestor values are to be returned. A positive integer defines a generation reference. A negative number or value of 0 defines a level reference. You must specify a dimName for every genLevNum. |
mbrName | Optional. Any valid single member name or member combination, or a function that returns a single member or member combination, from which the ancestor values are to be returned. |
Marketing expenses are captured at the Product Family and Region level in a product planning application. The Marketing Expense data must be allocated down to each Product code and State level based on Sales contribution. Data is captured as follows:
Sales Marketing ===== ========= New York 100-10 300 N/A 100-20 200 N/A 100 500 N/A Boston 100-10 100 N/A 100-20 400 N/A 100 500 N/A East 100-10 400 N/A 100-20 600 N/A 100 1000 200
The Marketing Expense value of 200 at East and Product code 100 is allocated down to each Product code and State with the following formula:
Marketing = (Sales / @MDANCESTVAL(2, Market, 2, Product, 2, Sales)) * @MDANCESTVAL(2, Market, 2, Product, 2, Marketing);
which produces the following result:
Sales Marketing ===== ========= New York 100-10 300 60 100-20 200 40 100 500 N/A Boston 100-10 100 20 100-20 400 80 100 500 N/A East 100-10 400 N/A 100-20 600 N/A 100 1000 N/A
The Marketing expenses can then be reconsolidated across Products and Markets.
@ANCESTVAL
@SANCESTVAL
@MDPARENTVAL
The @MDPARENTVAL() function returns parent-level data from multiple dimensions based on the current member being calculated.
@MDPARENTVAL(numDim, dimName1, . . . dimNameX [,mbrName])
numDim | Integer value that defines the number of dimensions from which parent values are being returned. |
dimName1, . . . dimNameX | Defines the dimension names from which the parent values are to be returned. |
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination, from which the parent values are to be returned. |
Marketing expenses are captured at the Product Family and Region level in a product planning application. The Marketing Expense data must be allocated down to each Product code and State level based on Sales contribution.
Data is captured as follows:
Sales Marketing ===== ========= New York 100-10 300 N/A 100-20 200 N/A 100 500 N/A Boston 100-10 100 N/A 100-20 400 N/A 100 500 N/A East 100-10 400 N/A 100-20 600 N/A 100 1000 200
The Marketing Expense value of 200 at East and Product code 100 is allocated down to each Product code and State with the following formula:
Marketing = (Sales / @MDPARENTVAL(2, Market, Product, Sales)) * @MDPARENTVAL(2, Market, Product, Marketing);
which produces the following result:
Sales Marketing ===== ========= New York 100-10 300 60 100-20 200 40 100 500 N/A Boston 100-10 100 20 100-20 400 80 100 500 N/A East 100-10 400 N/A 100-20 600 N/A 100 1000 N/A
The Marketing expenses can then be reconsolidated across Products and Markets.
@PARENTVAL
@SPARENTVAL
@MDANCESTVAL
The @MDSHIFT() function shifts a series of data values across multiple dimension ranges.
@MDSHIFT (mbrName, shiftCnt1, dimName1, [range1|(range1)], . . . shiftCntX, dimNameX, [rangeX|(rangeX)])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination, from which the values are to be shifted. |
shiftCnt1...shiftCntX | Integer that defines the number of member positions to shift. |
dimName1, . . . dimNameX | Defines the dimension names in which the shift is to occur. |
range1|(range1) . . . rangeX|(rangeX) | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension specified with the dimName parameter. If the range list is comma delimited, then the list must be enclosed in parentheses. |
The Budget figures for Ending Inventory need to be calculated by taking Prior Year->Opening Inventory results as a starting point:
Jan Feb Mar Prior Year Opening Inventory 110 120 130 . . Budget Ending Inventory N/A N/A N/A . .
The following calculation script assumes that the Scenario dimension is as follows:
Scenario Prior Year Budget
FIX (Budget) "Ending Inventory" = @MDSHIFT("Opening Inventory", 1, Year, , -1, Scenario,); ENDFIX
In this example, range1 is not specified, so Essbase defaults to the level 0 members of the Year dimension, which was specified as the dimName1 parameter. Since range2 is also not specified, Essbase defaults to the level 0 members of the Scenario dimension, which was specified as the dimName2 parameter. This example produces the following result:
Jan Feb Mar === === === Prior Year Opening Inventory 110 120 130 . . Budget Ending Inventory 120 130 140 . .
The @MEDIAN() function returns the median (the middle number) of the specified data set (expList). Half the numbers in the data set are larger than the median, and half are smaller.
@MEDIAN (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)
SKIPNONE | Includes all cells specified in expList, regardless of their content, during calculation of the median. |
SKIPMISSING | Excludes all #MISSING values from expList during
calculation of the median. |
SKIPZERO | Excludes all zero (0) values from expList during calculation of the median. |
SKIPBOTH | Excludes all zero (0) values and #MISSING values from
expList during calculation of the median. |
expList | Comma-delimited list of member specifications, variable names, functions, or numeric expressions. expList provides a list of numeric values across which the median is calculated. |
#MISSING
values as 0 unless SKIPMISSING
or SKIPBOTH is specified.The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Median. This example calculates the median sales values for all products and uses the @RANGE function to generate expList:
FIX (Product) Median = @MEDIAN(SKIPBOTH,@RANGE(Sales,@CHILDREN(Product))); ENDFIX
This example produces the following report:
Jan New York Actual Budget ====== ====== Sales Colas 678 640 Root Beer 551 530 Cream Soda 663 510 Fruit Soda 587 620 Diet Drinks #MI #MI Product 2479 2300 Median Product 625 575
Because SKIPBOTH is specified in the calculation script, the #MI
values for Diet Drinks are skipped. The remaining four products create an
even-numbered data set. So, to calculate Median->Product->Actual,
the two middle numbers in the set (587 and 663) are averaged to create the
median (625). To calculate Median->Product->Budget, the two middle
numbers in the set (530 and 620) are averaged to create the median (575).
The @MEMBER function returns the member with the name that is provided as a character string.
@MEMBER (String)
String | A string (enclosed in double quotation marks) or a function that returns a string |
Typically, the @MEMBER function is used in combination with string functions that are used to manipulate character strings to form the name of a member. In the following example, the member name QTR1 is appended to the character string 2000_ to form the string 2000_QTR1. The @MEMBER function returns the member 2000_QTR1 and QTD is set to the value of this member.
QTD=@MEMBER(@CONCATENATE("2000_", QTR1));
The @MERGE() function merges two member lists that are processed by another function. Duplicate values from both lists are included only once in the merged list.
@MERGE(list1, list2)
list1 | The first list of member specifications to be merged. |
list2 | The second list of member specifications to be merged. |
In the Sample Basic database,
@MERGE(@CHILDREN(Colas),@CHILDREN("Diet Drinks"));
returns Cola, Diet Cola, Caffeine Free Cola, Diet Root Beer, and Diet Cream Soda.
Diet Cola appears only once in the merged list, even though it is a child of both Colas and Diet Drinks.
In this example, the @MERGE function is used with the @ISMBR function to increase the marketing budget for major markets and for western markets.
Budget (IF (@ISMBR(@MERGE(@UDA(Market,"Major Market"), @DESCENDANTS(West)))) Marketing = Marketing * 1.1; ENDIF;);
This example produces the following report, which shows only the major markets in the East and all western markets:
Product Year Budget Marketing ========= New York 6039 Massachusetts 1276 Florida 2530 California 7260 Oregon 2090 Washington 2772 Utah 1837 Nevada 4521
The values prior to running the calculation script were:
New York 5490 Massachusetts 1160 Florida 2300 California 6600 Oregon 1900 Washington 2520 Utah 1670 Nevada 4110
The @MIN() function returns the minimum value among the results of the expressions in expList.
@MIN (expList)
expList | Comma-delimited list of members, variable names, functions, and numeric expressions, all of which return numeric values. |
Depending on the values in the list, @MIN may return a zero(0) or #MISSING
value. For full control over skipping or inclusion of zero(0) and #MISSING
values, it is recommended to use the @MINS function instead
of the @MIN function.
In the Sample Basic database:
Qtr1 = @MIN(Jan:Mar);
This example produces the following report:
Colas New York Actual Jan Feb Mar Qtr1 === === === ==== Sales 678 645 675 645
The @MINRANGE() function returns the minimum value of mbrName across rangeList.
@MINRANGE (mbrName [ ,rangeList])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
Depending on the values in the list, @MINRANGE may return a zero(0)
or #MISSING
value. For full control over skipping or inclusion
of zero(0) and #MISSING
values, it is recommended to use the
@MINSRANGE function instead of the @MINRANGE
function.
In the Sample Basic database:
Qtr1 = @MINRANGE(Sales,Jan:Mar);
This example produces the following report:
Colas New York Actual Jan Feb Mar Qtr1 === === === ==== Sales 678 645 675 645
The @MINS() function returns the minimum value across the results
of the expressions in expList, allowing skipping of #MISSING
and 0 values. @MINS enables skipping of #MISSING
and
0 values, in contrast with the @MIN function, which
always includes these values.
@MINS (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)
SKIPNONE | Includes in the operation all cells specified in expList regardless of their content |
SKIPMISSING | Ignores all #MISSING values |
SKIPZERO | Ignores all 0 values |
SKIPBOTH | Ignores all 0 and #MISSING values |
expList | Comma-delimited list of member names, variable names, functions, or numeric expressions. expList provides a list of numeric values for which Essbase determines the minimum value. |
#MISSING
values are less than positive data values
and more than negative data values, if the data being calculated includes
only positive and #MISSING
values, @MINS returns #MISSING.
#MISSING
values, @MINS may return either #MISSING
or 0 values
in an unpredictable manner.For both examples, assume a database similar to Sample Basic. The Measures
dimension includes two members: COGS (cost of goods sold) and OtherInc_Exp
(miscellaneous income and expenses). The data can include 0 and #MISSING
values.
Qtr1_Min = @MINS(SKIPBOTH, Jan:Mar);
This example ignores #MISSING
and 0 values for all members
of the Measures dimension. This example produces the following results:
Jan Feb Mar Qtr1_Min ======== ======== ======== ======== COGS #MISSING 1500 2300 1500 OtherInc_Exp -500 -350 0 -500
Qtr1_Min = @MINS(SKIPNONE, Jan:Mar);
For all members of the Measures dimension, this example includes #MISSING
and 0 values and produces the following results:
Jan Feb Mar Qtr1_Min ======== ======== ======== ======== COGS #MISSING 1500 2300 #MISSING OtherInc_Exp -500 -350 0 -500
The @MINSRANGE() function returns the minimum value of mbrName
across rangeList. @MINSRANGE enables skipping of #MISSING
and 0 values, in contrast with the @MINRANGE function,
which always includes these values in the calculation.
@MINSRANGE (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, mbrName [,rangeList])
SKIPNONE | Includes in the operation all specified cells regardless of their content |
SKIPMISSING | Ignores all #MISSING values |
SKIPZERO | Ignores all 0 values |
SKIPBOTH | Ignores all 0 and #MISSING values |
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
#MISSING
values are considered to be less than positive
data values and more than negative data values. Iif the data being calculated
includes only positive and #MISSING
values, @MINSRANGE
returns #MISSING.
For both examples, assume a database similar to Sample Basic. The Measures
dimension includes two members: COGS (cost of goods sold) and OtherInc_Exp
(miscellaneous income and expenses). The data can include 0 and #MISSING
values. For both members of the Measures dimension, the result is the same--the
minimum value for the OtherInc_Exp member across the specified range.
Qtr1_Min = @MINSRANGE(SKIPBOTH, OtherInc_Exp, Jan:Mar);
This example ignores the 0 value for Mar and produces the following results:
Jan Feb Mar Qtr1_Min ======== ======== ======== ======== COGS #MISSING 1500 2300 350 OtherInc_Exp 500 350 0 350
Qtr1_Min = @MINS(SKIPNONE, OtherInc_Exp, Jan:Mar);
This example does not ignore the 0 value in the calculation. This example produces the following results:
Jan Feb Mar Qtr1_Min ======== ======== ======== ======== COGS #MISSING 1500 2300 0 OtherInc_Exp 500 350 0 0
The @MOD() function calculates the modulus of a division operation.
@MOD (mbrName1, mbrName2)
mbrName1 and mbrName2 | Members from the same dimension whose modulus is to be calculated. |
The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Factor. The modulus between Profit % and Margin % is calculated with the following formula:
Factor = @MOD("Margin %", "Profit %");
This example produces the following report:
Market Product Scenario Margin % Profit % Factor ======== ======== ====== Jan 55.10 25.44 4.22 Feb 55.39 26.03 3.34 Mar 55.27 25.87 3.53
The @MODE() function returns the mode (the most frequently occurring value) in the specified data set (expList).
@MODE (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)
SKIPNONE | Includes all cells specified in expList, regardless of their content, during calculation of the mode. |
SKIPMISSING | Excludes all #MISSING values from expList during
calculation of the mode. |
SKIPZERO | Excludes all zero (0) values from expList during calculation of the mode. |
SKIPBOTH | Excludes all zero (0) values and #MISSING values from
expList during calculation of the mode. |
expList | Comma-delimited list of member specifications, variable names, functions, or numeric expressions. expList provides a list of numeric values across which the mode is calculated. |
#MISSING
is the mode of expList, @MODE
returns #MISSING
unless SKIPMISSING or SKIPBOTH is specified.
If you specify SKIPMISSING or SKIPBOTH and all values in expList
are #MISSING
, @MODE returns #MISSING
.
If you specify SKIPZERO or SKIPBOTH and all values in expList are
0, @MODE returns #MISSING
.The following example calculates the mode of the units sold for the Central region and uses the @RANGE function to generate expList:
FIX (Central) "Mode" = @MODE(SKIPMISSING, @RANGE(Sales,@CHILDREN(Central))); ENDFIX
This example produces the following report:
Colas Actual Jan Units Sold ========== Units Sold Illinois 3 Ohio 2 Wisconsin 3 Missouri #MI Iowa 0 Colorado 6 Central 14 Mode Central 3
The @MOVAVG() function applies a moving n-term average (mean) to an input data set. Each term in the set is replaced by a trailing mean of n terms, and the first terms (the n-1 terms) are copies of the input data. @MOVAVG modifies a data set for smoothing purposes.
@MOVAVG (mbrName [, n [, rangeList]])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
n | Optional. A positive integer value that represents the number of values to average. The default is 3. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
Trailing Average Centered Average 1 2 3 1 2 3 2 2
#MISSING
values and decreases the denominator accordingly.
For example, if one value out of three is #MISSING
, Essbase
adds the remaining two values and divides the sum by two. The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Mov Avg.
"Mov Avg" = @MOVAVG(Sales,3,Jan:Jun);
In this example, the @MOVAVG function smooths sales data for the first six months of the year (Jan through Jun). The results of @MOVAVG can be used with the @TREND function to forecast average sales data for a holiday season (for example, October - December).
This example produces the following report:
Colas New York Actual Sales Mov Avg ===== ======= Jan 678 678 Feb 645 645 Mar 675 666 Apr 712 677.3 May 756 714.3 Jun 890 786
In this example, Essbase averages three values at a time for the moving average. The first two values (Jan,Feb) for Mov Avg and the first two values for Sales are the same. The value for Mar represents the trailing average of Jan, Feb, and Mar. The value for Apr represents the trailing average of Feb, Mar, and Apr. The remaining values represent the trailing average for each group of three values.
@MOVMAX
@MOVMED
@MOVMIN
@TREND
The @MOVMAX() function applies a moving n-term maximum (highest number) to an input data set. Each term in the set is replaced by a trailing maximum of n terms, and the first terms (the n-1 terms) are copies of the input data. @MOVMAX modifies a data set for smoothing purposes.
@MOVMAX (mbrName [, n [, rangeList]])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
n | Optional. A positive integer value that represents the number of values that are used to calculate the moving maximum. The default is 3. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
Trailing Maximum Centered Maximum 1 2 3 1 2 3 3 3
#MISSING
values. For example, if one value out of four is #MISSING
,
@MOVMAX calculates the maximum of the remaining three values. The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Mov Max.
"Mov Max" = @MOVMAX(Sales,3,Jan:Jun);
In this example, the @MOVMAX function smooths sales data for the first six months of the year (Jan through Jun). The results of @MOVMAX can be used with the @TREND function to forecast maximum sales data for a holiday season (for example, October - December).
This example produces the following report:
Root Beer New York Actual Sales Mov Max ===== ======= Jan 551 551 Feb 641 641 Mar 586 641 Apr 630 641 May 612 630 Jun 747 747
In this example, Essbase uses three values at a time to calculate the moving maximum. The first two values (Jan,Feb) for Mov Max and the first two values for Sales are the same. The value for Mar represents the trailing maximum of Jan, Feb, and Mar. The value for Apr represents the trailing maximum of Feb, Mar, and Apr. The remaining values represent the trailing maximum for each group of three values.
@MOVAVG
@MOVMED
@MOVMIN
@TREND
The @MOVMED() function applies a moving n-term median (middle number) to an input data set. Each term in the list is replaced by a trailing median of n terms, and the first terms (the n-1 terms) are copies of the input data. @MOVMED modifies a data set for smoothing purposes.
@MOVMED (mbrName [, n [, rangeList]])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
n | Optional. A positive integer value that represents the number of values that are used to calculate the moving median. The default is 3. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
#MISSING
values. For example, if one value out of four is
#MISSING
, @MOVMED calculates the median of the remaining
three values.Trailing Median Centered Median 1 2 3 1 2 3 2 2
The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Mov Med.
"Mov Med" = @MOVMED(Sales,3,Jan:Jun);
In this example, the @MOVMED function smooths sales data for the first six months of the year (Jan through Jun). The results of @MOVMED could be used with the @TREND function to forecast sales data for a holiday season (for example, October - December).
This example produces the following report:
Colas New York Actual Sales Mov Med ===== ======= Jan 678 678 Feb 645 645 Mar 675 675 Apr 712 675 May 756 712 Jun 890 756
In this example, Essbase uses three values at a time to calculate the moving median. The first two values (Jan,Feb) for Mov Med are the same as the first two values for Sales. The value for Mar represents the trailing median of Jan, Feb, and Mar. The value for Apr represents the trailing median of Feb, Mar, and Apr. The remaining values represent the trailing median of each group of three values.
@MOVAVG
@MOVMAX
@MOVMIN
@TREND
The @MOVMIN() function applies a moving n-term minimum (lowest number) to an input data set. Each term in the list is replaced by a trailing minimum of n terms, and the first terms (the n-1 terms) are copies of the input data. @MOVMIN modifies a data set for smoothing purposes.
@MOVMIN (mbrName [, n [, rangeList]])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
n | Optional. A positive integer value that represents the number of values that are used to calculate the moving minimum. The default is 3. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
#MISSING
values. For example, if one value out of four is
#MISSING
, @MOVMIN calculates the minimum of the remaining
three values.Trailing Minimum Centered Minimum 1 2 3 1 2 3 1 1
The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Mov Min.
"Mov Min" = @MOVMIN(Sales,3,Jan:Jun);
In this example, the @MOVMIN function smooths sales data for the first six months of the year (Jan through Jun). The results of @MOVMIN can be used with the @TREND function to forecast minimum sales data for the holiday season (for example, October - December).
This example produces the following report:
Colas New York Actual Sales Mov Min ===== ======= Jan 678 678 Feb 645 645 Mar 675 645 Apr 712 645 May 756 675 Jun 890 712
In this example, Essbase uses three values at a time to calculate the moving minimum. The first two values (Jan,Feb) for Mov Min and the first two values for Sales are the same. The value for Mar represents the trailing minimum of Jan, Feb, and Mar. The value for Apr represents the trailing mimimum of Feb, Mar, and Apr. The remaining values represent the trailing minimum for each group of three values.
@MOVAVG
@MOVMAX
@MOVMED
@TREND
The @NAME() function passes the enclosed string or member name as a string to another function.
@NAME (mbrName)
mbrName | Any valid single member name, dimension name, or a string. |
Essbase does not support strings in functions. It treats strings as values or array of values. The @NAME function processes strings.
The following example is based on the Sample Basic database. A user defined function is used to retrieving the price from the table below. The user defined function (J_GetPrice) takes two string parameters time and product name to return the price for each product.
MonthName | ProductId | Price |
Jan | 100-10 | 1.90 |
Feb | 100-10 | 1.95 |
Mar | 100-10 | 1.98 |
Jan | 100-20 | 1.95 |
Feb | 100-20 | 2.00 |
Mar | 100-20 | 2.05 |
Price = @J_GetPrice(@NAME(@CURRMBR(Product)),@NAME(@CURRMBR(Year)));
The following report illustrates the above example:
Price Actual Market Jan Feb Mar === === === 100-10 1.90 1.95 1.98 100-20 1.95 2.00 2.05
The @NEXT() function returns the nth cell value in the sequence rangeList from mbrName, retaining all other members identical to the current member. @NEXT cannot operate outside the given range.
@NEXT (mbrName [, n, rangeList])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
n | Optional signed integer. If you do not specify n, then the default is set to 1, which provides the next member in the range. Using a negative value for n has the same effect as using the matching positive value in the @PRIOR function. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
In this example, Next Cash for each month is derived by taking the Cash value for the following month. Since n is not specified, the default is 1, which provides the next member in the range. Since rangeList is not specified, the level 0 members from the dimension tagged as Time are used (Jan,Feb,Mar, ...).
"Next Cash" = @NEXT(Cash);
This example produces the following report:
Jan Feb Mar Apr May Jun === === === === === === Cash 100 90 120 110 150 100 Next Cash 90 120 110 150 100 #MI
@PRIOR
@SHIFT
@SHIFTMINUS
@SHIFTPLUS
The @NEXTS() function returns the nth cell value in the sequence
rangeList from the mbrName. @NEXTS provides the option
to skip #MISSING
, zero, or both #MISSING
and zero
values. @NEXTS works within a designated range and retains all other
members identical to the current member.
@NEXTS(SKIPNONE | SKIPMISSING | SKIPZERO | | SKIPBOTH mbrName[,n,rangeList])
SKIPNONE | Includes all cells specified in the rangeList operation, regardless of their content. |
SKIPMISSING | Ignores all #MISSING values in the rangeList
operation. |
SKIPZERO | Ignores all 0 in the rangeList operation. |
SKIPBOTH | Ignores all #MISSING and 0 values in the rangeList
operation. |
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
n | Optional signed integer. Using a negative value for n has the same effect as using the matching positive value in @PRIORS. If you do not specify n, then a default value of 1 is assumed, which returns the next prior member from the lowest level of the dimension set as Time in the database outline. |
rangeList | Optional. A valid member, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension set as Time. |
In this example, Next Cash for each month is derived by taking the Cash
value for the following month and ignoring both #MISSING
and
zero values. Because n is not specified, the default is 1, which
provides the next member in the range. Also, because rangeList is
not specified, the level 0 members from the dimension set as Time are used
(Jan,Feb,Mar, ...).
"Next Cash" = @NEXTS(SKIPBOTH, Cash);
The following report illustrates the above example:
Jan Feb Mar Apr May Jun === === === === === === Cash 1100 #MI 1000 1300 0 1400 Next Cash 1000 1000 1300 1400 1400 #MI
The @NPV() function calculates the Net Present Value of an investment based on the series of payments (negative values) and income (positive values).
@NPV (cashflowMbr, rateMbrConst, discountFlag [, rangeList])
cashflowMbr | Member specification providing a series of numeric values. |
rateMbrConst | Single member specification, variable name, or numeric expression, providing a constant value. |
discountFlag | Single member specification, variable name, or numeric expression set to 0 or 1 to indicate whether the function should discount from the first period. 1 means do not discount from the first period. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the dimension tagged as Time. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
In this example, Value is calculated with the following formula:
Value = @NPV(Cash, Rate, 0, FY1990:FY1994, FY1995:FY2000);
This example produces the following report:
FY1990 FY1991 FY1992 FY1993 FY1994 FY1995 ====== ====== ====== ====== ====== ====== Cash (1,000) 500 600 500 #MISSING #MISSING Rate 0 0 0 0 #MISSING #MISSING Value 296 296 296 296 296 296
The @PARENT () function returns the parent of the current member being calculated in the specified dimension. If you specify the optional mbrName, that parent is combined with the specified member.
This member set function can be used as a parameter of another function, where that parameter is a member or list of members.
@PARENT(dimName [, mbrName])
dimName | Single dimension name specification. |
mbrName | Optional. Any valid single member name or member combination, or a function that returns a single member or member combination, that is combined with the parent returned. |
Sales(@PARENT(Product) = 5;);
Sales = @PARENT(Profit); Sales = @PARENTVAL(Profit);
In this case, using the latter formula results in better calculation performance. In general, use @PARENT as a member rather than as an implied value of a cell. For example:
Sales = @AVG(SKIPMISSING, @ISIBLINGS(@PARENT("100")));
In the Sample Basic database:
@PARENT(Market,Sales)
returns Central->Sales, if the current member of Market being calculated is Colorado.
@PARENT(Measures)
returns Profit, if the current member of Measures being calculated is Margin.
@ANCEST
@CHILDREN
@ANCESTORS
@DESCENDANTS
@SIBLINGS
The @PARENTVAL() function returns the parent values of the member being calculated in the specified dimension.
@PARENTVAL (dimName [, mbrName])
dimName | Single dimension name specification that defines the focus dimension of parent values. |
mbrName | Optional. Any valid single member name or member combination, or a function that returns a single member or member combination. |
This example is based on the Sample Basic database. The formula calculates Market Share for each state by taking each state's Sales value as a percentage of Sales for East (its parent) as a whole. Market Share->East is calculated as East's percentage of its parent, Market.
"Market Share" = Sales % @PARENTVAL(Market,Sales);
This example produces the following report:
Cola Actual Jan Sales Market Share ===== ============ New York 678 37.42 Massachusetts 494 27.26 Florida 210 11.59 Connecticut 310 17.11 New Hampshire 120 6.62 East 1812 37.29 Market 4860 100
@MDPARENTVAL
@SPARENTVAL
@ANCESTVAL
The @POWER() function returns the value of the specified member or expression raised to power.
@POWER (expression,power)
expression | Single member specification, variable name, function, or other numeric expression. |
power | Single member specification, variable name, function, or other numeric expression. |
#MISSING
.#MISSING
.@POWER(14,3) 2744 @POWER (2,8) 256
The @PRIOR() function returns the nth previous cell member from mbrName in rangeList. All other dimensions assume the same members as the current member. @PRIOR works only within the designated range.
@PRIOR (mbrName [, n, rangeList])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
n | Optional signed integer. Using a negative value for n has the same effect as using the matching positive value in the @NEXT function. If you do not specify n, then a default value of 1 is assumed, which returns the next prior member from the lowest level of the dimension tagged as Time in the database outline. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
In this example, Prev Inventory for each month is derived by taking the Inventory value from the previous month. Since n is not specified, the default is 1, which provides the next prior member in the range. Since rangeList is not specified, the level 0 members from the dimension tagged as Time are used (Jan,Feb,Mar,...).
"Prev Inventory" = @PRIOR(Inventory);
This example produces the following report:
Jan Feb Mar Apr May Jun === === === === === === Inventory 1100 1200 1000 1300 1300 1400 Prev Inventory #MI 1100 1200 1000 1300 1300
@NEXT
@SHIFT
@SHIFTMINUS
@SHIFTPLUS
The @PRIORS() function returns the nth previous cell member
from mbrName in the rangeList. @PRIORS provides options
to skip #MISSING
, zero, or both #MISSING
and zero
values. All other dimensions assume the same members as the current member.
@PRIORS works within the designated range.
@PRIORS(SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH mbrName[,n, rangeList])
SKIPNONE | Includes all cells specified in the rangeList operation regardless of their content. |
SKIPMISSING | Ignores all #MISSING values in the rangeList
operation. |
SKIPZERO | Ignores all zero values in the rangeList operation. |
SKIPBOTH | Ignores all #MISSING and zero values in the rangeList
. |
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
n | Optional signed integer. Using a negative value for n has the same effect as using the matching positive value in the @NEXTS function. If you do not specify n, then a default value of 1 is assumed, which returns the next prior member from the lowest level of the dimension set as Time in the database outline. |
rangeList | Optional. A valid member, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension set as Time. |
In this example, Prev Inventory for each month is derived by taking the
Inventory value from the previous month and ignoring #MISSING
and zero values. Because n is not specified, the default is 1, which
provides the next prior member in the range. Also, because rangeList is
not specified, the level 0 members from the dimension are set as Time used
as (Jan,Feb,Mar,...).
"Prev Inventory" = @PRIORS(SKIPBOTH,Inventory);
The following report illustrates this example:
Jan Feb Mar Apr May Jun === === === === === === Inventory 1100 #MI 1000 1300 0 1400 Prev Inventory #MI 1100 1100 1000 1300 1300
The @PTD() function calculates the period-to-date values of members in the dimension tagged as Time. By default, data is summed unless Accounts are tagged as "First" or "Last".
@PTD (timePeriodList)
timePeriodList | Range of members from the dimension tagged as Time. |
In this example, assume that the Year dimension in the Sample Basic database outline contains two additional members, YTD and QTD. Using a calculation script, the YTD and QTD members are calculated as follows:
YTD = @PTD(Jan:May); QTD = @PTD(Apr:May);
In this example Opening Inventory is tagged with a time balance of First, and Ending Inventory is tagged with a time balance of Last.
This example produces the following report:
Product Market Scenario Sales Opening Inventory Ending Inventory ===== ================= ================ Jan 31538 117405 116434 Feb 32069 116434 115558 Mar 32213 115558 119143 Qtr1 95820 117405 119143 Apr 32917 119143 125883 May 33674 125883 136145 Jun 35088 136145 143458 Qtr2 101679 119143 143458 QTD 66591 245026 262028 YTD 162411 362431 381171
The @RANGE() function returns a member list that crosses the specified member from one dimension (mbrName) with the specified member range from another dimension (rangeList). @RANGE can be combined with non-range functions, such as @AVG, which replaces an existing range function, such as @AVGRANGE.
@RANGE(mbrName [, rangeList])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
The following example is based on the Sample Basic database. The @RANGE function is used with the @AVG function to determine the average sales for Colas in the West.
FIX(Sales) West=@AVG(SKIPBOTH,@RANGE(Sales,@CHILDREN(West))); ENDFIX
Since the calculation script fixes on Sales, only the Sales value for West are the average of the values for western states; COGS values for West are the sum of the western states. This example produces the following report:
Colas Sales COGS Actual Actual Qtr3 Qtr4 Qtr3 Qtr4 ==== ==== ==== ==== California 3401 2767 2070 1701 Oregon 932 1051 382 434 Washington 1426 1203 590 498 Utah 1168 1294 520 575 Nevada 496 440 222 197 West 1484.6 1351 3784 3405
The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Prod Count. The @RANGE function is used with the @COUNT function to calculate the count of all products for which a data value exists:
"Prod Count" = @COUNT(SKIPMISSING,@RANGE(Sales,@CHILDREN(Product)));
This example produces the following report. Since SKIPMISSING is specified
in the formula, the #MI
value for Sales->Diet Drinks is
not counted as a data value:
Jan New York Actual Sales Prod Count ===== ========== Colas 678 #MI Root Beer 551 #MI Cream Soda 663 #MI Fruit Soda 587 #MI Diet Drinks #MI #MI Product 2479 4
Calculator function @RANGE and the cross-dimensional operator (->) cannot be used inside a FIX/ENDFIX statement.
The @RANK() function returns the rank of the specified members or the specified value among the values in the specified data set. The rank of a value is equivalent to its position (its rank) in the sorted data set.
@RANK (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, value, expList)
SKIPNONE | Includes all cells specified in expList, regardless of their content, during calculation of the rank. |
SKIPMISSING | Excludes all #MISSING values from expList during
calculation of the rank. |
SKIPZERO | Excludes all zero (0) values from expList during calculation of the rank. |
SKIPBOTH | Excludes all zero (0) values and #MISSING values from
expList during calculation of the rank. |
value | (1) The member or member combination for which the rank is calculated, or (2) a constant value for which the rank is calculated. |
expList | Comma-delimited list of member specifications, variable names, functions, or numeric expressions. expList provides a list of numeric values across which the rank is calculated. |
#MISSING
returns #MISSING
.
#MISSING
is also returned if, after SKIP processing, there
are no values to compare.The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Sales Rank. Essbase ranks the sales values for a set of products:
"Sales Rank" = @RANK(SKIPBOTH,Sales, @RANGE(Sales,@LEVMBRS(Product,1)));
This example produces the following report. Since SKIPBOTH is specified in
the formula, the #MI
value for Sales->Diet Drinks is not included
in the ranked list:
New York Actual Jan Sales Sales Rank ===== ========== Colas 678 1 Root Beer 551 4 Cream Soda 663 2 Fruit Soda 587 3 Diet Drinks #MI #MI
The @RDESCENDANTS function returns all descendants of the specified member, or those down to the specified generation or level. This function excludes the specified member. If one or more of the descendants are shared, the result also includes either all descendants of each member being shared or descendants down to the specified generation or level.
You can use this member set function as a parameter of another function, where that parameter is a list of members. In the absence of shared members, @RDESCENDANTS and @DESCENDANTS return the same result.
@RDESCENDANTS (mbrName [, genLevNum| genLevName])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination |
genLevNum | Optional. An integer value that defines the absolute generation or level number down to which to select the members. A positive integer defines a generation number. A value of 0 or a negative integer defines a level number. |
genLevName | Optional. Level name or generation name down to which to select the members. |
Assume a variation of the Sample Basic database such that the Product dimension includes the following members:
Product 100 100-10 100-20 100-30 200 200-10 200-20 200-30 200-40 Diet 100 (Shared Member) 200 (Shared Member)
Diet has two children "100" and "200". The members "100" and "200" are shared members.
@RDESCENDANTS(Diet)
returns the members: 100, 100-10, 100-20, 100-30, 200, 200-10, 200-20, 200-30, 200-40 (in that order).
@RDESCENDANTS(Profit)
returns Margin, Sales, COGS, Total Expenses, Marketing, Payroll, and Misc (in that order) and is identical to @DESCENDANTS(Profit).
@DESCENDANTS
@IRDESCENDANTS
@IDESCENDANTS
@ISDESC
@ANCESTORS
@CHILDREN
@SIBLINGS
The @RELATIVE function returns all members at the specified generation or level that are above or below the specified member in the database outline.
@RELATIVE (mbrName, genLevNum | genLevName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
genLevNum | An integer value that defines the number of a generation or level. A positive integer defines a generation number. A value of 0 or a negative integer defines a level number. |
genLevName | Generation or level name specification. |
@RELATIVE(200,0)
,
Essbase returns 200-10, 200-20, 200-30, 200-40 (in that order). This order
is important to consider when you use the @RELATIVE member set
function with certain forecasting and statistical functions.In the Sample Basic database:
@RELATIVE(Qtr1,3) @RELATIVE(Qtr1,0)
both return the three members that are at generation 3 (or level 0) and that are below Qtr1 in the Sample Basic outline: Jan, Feb, and Mar (in that order).
@RELATIVE(Profit,-1)
returns the two members that are at level 1 and that are below Profit: Margin and Total Expenses (in that order).
The @REMAINDER() function returns the remainder value of expression.
@REMAINDER (expression)
expression | Single member specification, variable name, or other numeric expression. |
Margin = @REMAINDER("Margin %");
This example produces the following report:
Product Market Margin % Margin Jan Feb Mar Jan Feb Mar === === === === === === Scenario 55.10 55.39 55.27 0.10 0.39 0.27
The @REMOVE() function removes values or members in one list from another list.
@REMOVE (list1, list2)
list1 | A list of member specifications, from which the members specified in list2 are removed. |
list2 | A list of member specifications to be removed from list1. |
In the Sample Basic database,
@REMOVE(@CHILDREN(East),@LIST("New York",Connecticut))
returns Massachusetts, Florida, New Hampshire.
The following example is based on the Sample Basic database. Assume that the Market dimension contains an additional member, Non-West.
A special analysis requires a sum of the actual sales values of a particular product family for non-western states. In this example, the @REMOVE function is used with the @SUMRANGE function to perform this analysis. The @LIST function is used to group the last two arguments of the @REMOVE function (the children of West plus two additional members, Texas and New Mexico).
FIX(Sales) "Non-West"=@SUMRANGE(Sales,@REMOVE(@LEVMBRS(Market,0), @LIST(@CHILDREN(West),Texas,"New Mexico"))); ENDFIX
This example produces the following report:
Jan Colas Actual Sales ===== Non-West 5114 New York 678 Massachusetts 494 Florida 410 Connecticut 310 New Hampshire 213 East 2105 California 941 Oregon 450 Washington 320 Utah 490 Nevada 138 West 2339 Texas 642 Oklahoma 180 Louisiana 166 New Mexico 219 South 1207 Illinois 579 Ohio 430 Wisconsin 490 Missouri 360 Iowa 161 Colorado 643 Central 2663
The @ROUND() function rounds expression to numDigits.
@ROUND (expression,numDigits)
expression | Single member specification, variable name, or other numeric expression. |
numDigits | Single member specification, variable name, or other numeric expression that provides an integer value. If numDigits is 0, the number is rounded to the nearest integer. If numDigits is greater than 0, expression is rounded to the specified number of decimal places. If numDigits is a negative value, expression is rounded to a power of 10. The default value for numDigits is 0. |
The following example is based on the Sample Basic database:
SET UPDATECALC OFF; Profit = @ROUND("Profit_%", 1);
This example produces the following report:
Market Product Profit_% Profit Jan Feb Mar Jan Feb Mar === === === === === === Scenario 21.37 19.09 18.46 21.4 19.1 18.5
@ABS
@INT
@TRUNCATE
@REMAINDER
The @RSIBLINGS() function returns all of the right siblings of the specified member. Right siblings are children that share the same parent as the member and that follow the member in the database outline. This function excludes the specified member.
This member set function can be used as a parameter of another function, where that parameter is a list of members.
@RSIBLINGS(mbrName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
@RSIBLINGS(Florida)
returns Connecticut and New Hampshire (in that order). These members appear below Florida in the Sample Basic outline.
@RSIBLINGS(Sales)
returns COGS because this member appears below Sales in the Sample Basic outline.
@IRSIBLINGS, which includes the specified member.
@LSIBLINGS
The @SANCESTVAL() function returns ancestor-level data based on the shared ancestor value of the current member being calculated.
@SANCESTVAL (rootMbr,genLevNum [, mbrName])
rootMbr | Defines a member that is used to search for the nearest occurrence of an ancestor of a shared member. |
genLevNum | Integer value that defines the absolute generation or level number from which the ancestor values are to be returned. A positive integer defines a generation reference. A negative number or value of 0 defines a level reference. |
mbrName | Optional. Any valid single member name or member combination, or a function that returns a single member or member combination, for which the ancestor values are to be returned. |
Marketing expenses are captured at the Product Category levels in a product planning application. The Product categories are defined as ancestors that contain shared members as children. The Marketing Expense data must be allocated down to each Product code based on Sales contribution.
The following Product hierarchy is defined:
Product 100 100-10 100-20 200 200-10 200-20 Diet ~ 100-10 SHARED 200-10 SHARED Caffeine Free ~ 100-20 SHARED 200-20 SHARED
Sales Marketing ===== ========= 100-10 300 0 100-20 200 0 100 500 0 200-10 100 0 200-30 400 0 200 900 0 100-10 300 0 200-10 100 0 Diet 400 50 100-20 200 0 200-30 400 0 Caffeine Free 600 40
The Marketing Expense value is allocated down to each Product code with the following formula:
Marketing = (Sales / @SANCESTVAL(Product, 2, Sales)) * @SANCESTVAL(Product, 2, Marketing);
which produces the following result:
Sales Marketing ===== ========= 100-10 300 37.5 100-20 200 13.3 100 500 #MI 200-10 100 12.5 200-30 400 26.7 200 900 #MI 100-10 300 37.5 200-10 100 12.5 Diet 400 50 100-20 200 13.3 200-30 400 26.7 Caffeine Free 600 40
The Marketing expenses can then be reconsolidated across Products and Markets.
@ANCESTVAL
@MDANCESTVAL
@SPARENTVAL
The @SHIFT() function returns the nth cell value in the sequence rangeList from mbrName, retaining all other members identical to the current member.
This function gets either the prior or next (nth past or future value in rangeList) value of mbrName, based on n. The direction of shift is wholly based on n, with positive n values producing an effect equivalent to @NEXT and negative values of n producing an equivalent effect to @PRIOR.
@SHIFT (mbrName [,n, rangeList])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
n | Optional signed integer. Using a negative value for n has the same effect as using a positive value in the @PRIOR function. n must be a numeric value, not a reference, such as a member name. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
In this example, Prev Asset for each month is derived by taking the Asset value from the previous month because -1 is specified as the n parameter. Next Avl Asset for each month is derived by taking the Asset value from two months following the current month because 2 is specified as the n parameter. Since rangeList is not specified for either formula, the level 0 members from the dimension tagged as Time are used.
"Prev Asset" = @SHIFT(Asset,-1); "Next Avl Asset" = @SHIFT(Asset,2);
This examples produces the following report:
Jan Feb Mar Apr May Jun === === === === === === Asset 100 110 105 120 115 125 Prev Asset #MI 100 110 105 120 115 Next Avl Asset 105 120 115 125 #MI #MI
@MDSHIFT
@NEXT
@PRIOR
@SHIFTPLUS
@SHIFTMINUS
The @SHIFTMINUS() function can be used in place of the @SHIFT() function, the @PRIOR() function, or the @NEXT() function to improve performance if the formula meets the following criteria:
X = Y - @SHIFT(mbrName [,n, rangeList])
X = Y - @PRIOR(mbrName [,n, rangeList])
X = Y - @NEXT(mbrName [,n, rangeList])
If these criteria are met, consider rewriting your formula using @SHIFTMINUS() instead. @SHIFTMINUS() runs the formula in block mode, improving performance.
@SHIFTMINUS (mbrName [,n, rangeList])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
n | Optional signed integer.
n must be a numeric value, not a reference, such as a member
name. If you are using @SHIFTPLUS to replace
the @NEXT function, use 1 as the value for n. If you are
using @SHIFTPLUS to replace the @PRIOR function, use -1 as
the value for n. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as time. |
The following example shows a formula using @SHIFT().
Sales = Loss - @SHIFT(Sales, 1);
Here is the formula using @SHIFTMINUS() to improve performance:
@SHIFTMINUS (Loss, Sales, 1)
@SHIFT
@SHIFTPLUS
@PRIOR
@NEXT
The @SHIFTPLUS() function can be used in place of the @SHIFT() function, the @PRIOR() function, or the @NEXT() function to improve performance if the formula meets the following criteria:
X = Y + @SHIFT(mbrName [,n, rangeList])
X = Y + @PRIOR(mbrName [,n, rangeList])
X = Y + @NEXT(mbrName [,n, rangeList])
If these criteria are met, consider rewriting your formula using @SHIFTPLUS() instead. @SHIFTPLUS() runs the formula in block mode, improving performance.
@SHIFTPLUS (mbrName [,n, rangeList])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
n | Optional signed integer.
n must be a numeric value, not a reference, such as a member
name. If you are using @SHIFTPLUS to replace
the @NEXT function, use 1 as the value for n. If you are using
@SHIFTPLUS to replace the @PRIOR function, use -1 as the value
for n. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as time. |
The following example shows a formula using @SHIFT().
Sales = Loss + @SHIFT(Sales, 1);
Here is the formula using @SHIFTPLUS() to improve performance:
@SHIFTPLUS (Loss, Sales, 1);
@SHIFT
@SHIFTMINUS
@PRIOR
@NEXT
The @SIBLINGS() function returns all siblings of the specified member. This function excludes the specified member.
This function can be used as a parameter of another function, where that parameter is a list of members.
@SIBLINGS (mbrName)
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
In the Sample Basic database:
@SIBLINGS (Washington)
returns Oregon, California, Utah, and Nevada (in that order).
@SIBLINGS(East)
returns West, South, and Central (in that order).
@ISIBLINGS, which includes the specified member.
@ISISIBLING
@ISSIBLING
@LSIBLINGS
@RSIBLINGS
The @SLN() function calculates the amount per period that an asset in the current period may be depreciated, calculated across a range of periods. The depreciation method used is straight-line depreciation.
More than one asset may be depreciated over the range. The value is depreciated from its entry period to the last period in the range. The resulting value represents the sum of all the per-period depreciation values of each asset being depreciated.
@SLN(costMbr, salvageMbrConst, lifeMbrConst [, rangeList])
costMbr | Single member specification representing an input asset for the current period. |
salvageMbrConst | Single member specification, variable name, or numeric expression, providing a constant numeric value. This value represents the value of the asset in the current period at the end of the useful life of the asset. |
lifeMbrConst | Single member specification, variable name, or numeric expression representing the useful life of the asset. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the dimension tagged as Time. rangeList represents the range over which the function accepts input and returns depreciation values. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
In this example, the depreciation for each year is calculated by taking into account the initial asset (Asset), the salvage value of the asset (Residual), and the life of the asset (Life).
"SLN Dep" = @SLN(Asset,Residual,Life,FY1991:FY1995);
This example produces the following report:
FY1991 FY1992 FY1993 FY1994 FY1995 FY1996 ====== ====== ====== ====== ====== ====== Asset 9,000 0 1,000 0 0 0 Residual 750.00 0.00 0.00 0.00 0 0 Life 5.00 #MI 5.00 0.00 0.00 0 SLN Dep 1650 1650 1850 1850 1850 0
The @SPARENTVAL() function returns parent-level data based on the shared parent value of the current member being calculated.
@SPARENTVAL(RootMbr [, mbrName])
RootMbr | Defines a member that is used to search for the nearest occurrence of a parent of a shared member. |
mbrName | Optional. Any valid single member name or member combination, or a function that returns a single member or member combination, from which the parent values are returned. |
Marketing expenses are captured at the Product Category levels in a product planning application. The Product categories are defined as parents that contain shared members as children. The Marketing Expense data must be allocated down to each Product code based on Sales contribution.
The following Product hierarchy is defined:
Product 100 100-10 100-20 200 200-10 200-20 Diet ~ 100-10 SHARED 200-10 SHARED Caffeine Free ~ 100-20 SHARED 200-20 SHARED
Sales Marketing ===== ========= 100-10 300 0 100-20 200 0 100 500 0 200-10 100 0 200-30 400 0 200 900 0 100-10 300 0 200-10 100 0 Diet 400 50 100-20 200 0 200-30 400 0 Caffeine Free 600 40
The Marketing Expense value is allocated down to each Product code with the following formula:
Marketing = (Sales / @SPARENTVAL(Product, Sales)) * @SPARENTVAL(Product, Marketing);
which produces the following result:
Sales Marketing ===== ========= 100-10 300 37.5 100-20 200 13.3 100 500 #Missing 200-10 100 12.5 200-30 400 26.7 200 900 #Missing 100-10 300 37.5 200-10 100 12.5 Diet 400 #Missing 100-20 200 13.3 200-30 400 26.7 Caffeine Free 600 #Missing
The Marketing expenses can then be reconsolidated across Products and Markets.
@PARENTVAL
@MDPARENTVAL
@SANCESTVAL
The @SPLINE() function applies a smoothing spline to a set of data points. A spline is a mathematical curve that smoothes or interpolates data.
@SPLINE (YmbrName [, s [, XmbrName [, rangeList]]])
YmbrName | A valid single member name that contains the dependent variable values used (when crossed with rangeList) to construct the spline. |
s | Optional. A zero (0) or positive value that determines the smoothness parameter. The default value is 1.0. |
XmbrName | Optional. A valid single member name that contains the independent variable values used (when crossed with rangeList) to construct the spline. The default independent variable values are 0,1,2,3, and so on. |
rangeList | Optional. A comma-delimited list of members, member set functions, or range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
#MISSING
values in YmbrName
and XmbrName; in the result, Essbase replaces the #MISSING
values of YmbrName with the spline values.The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Sales Spline. The formula calculates the spline of Sales values for Jan through Jun, based on a smoothness parameter of 2.
"Sales Spline" = @SPLINE(Sales,2,,Jan:Jun);
This example produces the following report:
Colas Actual New York Sales Sales Spline ===== ============ Jan 645 632.8941564 Feb 675 675.8247101 Mar 712 724.7394598 Apr 756 784.2860765 May 890 852.4398456 Jun 912 919.8157517
The @STDEV() function calculates the standard deviation of the specified data set (expList). The calculation is based upon a sample of a population. Standard deviation is a measure of how widely values are dispersed from their mean (average).
@STDEV (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)
SKIPNONE | Includes all cells specified in expList, regardless of their content, during calculation of the standard deviation. |
SKIPMISSING | Excludes all #MISSING values from expList during
calculation of the standard deviation. |
SKIPZERO | Excludes all zero (0) values from expList during calculation of the standard deviation. |
SKIPBOTH | Excludes all zero (0) values and #MISSING values from
expList during calculation of the standard deviation. |
expList | Comma-delimited list of member specifications, variable names, functions, or numeric expressions. expList provides a list of numeric values across which the standard deviation is calculated. |
The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Std Deviation. This example calculates the standard deviation (based on a sample of a population) of the sales values for all products and uses the @RANGE function to generate expList.
FIX (Product) "Std Deviation" = @STDEV(SKIPBOTH,@RANGE(Sales,@CHILDREN(Product))); ENDFIX
This example produces the following report:
Jan New York Actual Budget ====== ====== Sales Colas 678 640 Root Beer 551 530 Cream Soda 663 510 Fruit Soda 587 620 Diet Drinks #MI #MI Product 2479 2300 Std Deviation Product 60.73 64.55
The @STDEVP() function calculates the standard deviation of the specified data set (expList). The calculation is based upon the entire population. Standard deviation is a measure of how widely values are dispersed from their mean (average).
@STDEVP (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)
SKIPNONE | Includes all cells specified in expList, regardless of their content, during calculation of the standard deviation. |
SKIPMISSING | Excludes all #MISSING values from expList during
calculation of the standard deviation. |
SKIPZERO | Excludes all zero (0) values from expList during calculation of the standard deviation. |
SKIPBOTH | Excludes all zero (0) values and #MISSING values from
expList during calculation of the standard deviation. |
expList | Comma-delimited list of member specifications, variable names, functions, or numeric expressions. expList provides a list of numeric values across which the standard deviation is calculated. |
The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Std Deviation. This example calculates the standard deviation (based on the entire population) of the sales values for all products and uses the @RANGE function to generate expList.
FIX (Product) "Std Deviation" = @STDEVP(SKIPBOTH,@RANGE(Sales,@CHILDREN(Product))); ENDFIX This example produces the following report:
Jan New York Actual Budget ====== ====== Sales Colas 678 640 Root Beer 551 530 Cream Soda 663 510 Fruit Soda 587 620 Diet Drinks #MI #MI Product 2479 2300 Std Deviation Product 52.59 55.90
The @STDEVRANGE() function calculates the standard deviation of all values of the specified member (mbrName) across the specified data set (rangeList). The calculation is based upon a sample of a population. Standard deviation is a measure of how widely values are dispersed from their mean (average).
@STDEVRANGE (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH,
mbrName [, rangeList])
SKIPNONE | Includes all cells specified in expList, regardless of their content, during calculation of the standard deviation. |
SKIPMISSING | Excludes all #MISSING values from expList during
calculation of the standard deviation. |
SKIPZERO | Excludes all zero (0) values from expList during calculation of the standard deviation. |
SKIPBOTH | Excludes all zero (0) values and #MISSING values from
expList during calculation of the standard deviation. |
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Std Deviation. This example calculates the standard deviation (based on a sample of a population) of the sales values for all products.
FIX (Product) "Std Deviation" = @STDEVRANGE(SKIPBOTH,Sales,@CHILDREN(Product)); ENDFIX
This example produces the following report:
Jan New York Actual Budget ====== ====== Sales Colas 678 640 Root Beer 551 530 Cream Soda 663 510 Fruit Soda 587 620 Diet Drinks #MI #MI Product 2479 2300 Std Deviation Product 60.73 64.55
The @SUBSTRING function returns the requested string of characters from an existing source string. The source string can be a text string or a member name, or it can result from a specified function that returns a text string or a single member name.
@SUBSTRING (String, StartPosition [, EndPosition])
String | A string or member name, or a function that returns a string or a single member name |
StartPosition | Beginning character position within String to include in the substring. An integer greater than or equal to 0, where 0 corresponds to the first character in String, 1 corresponds to the second character, and so on. |
EndPosition | Optional. The first position past the last character to be included in the substring. An integer greater than or equal to 0, where 0 corresponds to the first character in String, 1 corresponds to the second character, and so on. If EndPosition is not specified or is less than StartPosition, Essbase returns all remaining characters from the source string. |
The following examples are based on the Sample Basic database:
Function statement | Result |
@SUBSTRING ("100-10",1) | "00-10" |
@SUBSTRING ("200-21",0,2) | "20" |
@SUBSTRING (@PARENT(Jan),3) (The parent of Jan is Qtr1.) |
"1" |
The @SUM() function returns the summation of all the values in expList.
@SUM (expList)
expList | Comma-delimited list of member specifications, variable names, or numeric expressions, all of which provide numeric values. |
In the Sample Basic database:
FIX("Total Expenses") West=@SUM(West,East); ENDFIX
Since the calculation script fixes on Total Expenses, the value for Total Expenses->West is equal to the sum of the value for East and the values for the states making up the West. For Sales, West and East are simply the sum of the states making up each region (that is, Sales->West is not equal to the sum of East and West). This example produces the following report:
Product Qtr1 Actual Sales Total Expenses ===== ============== New York 7705 2068 Massachusetts 3660 892 Florida 4132 1313 Connecticut 3472 1087 New Hampshire 1652 801 East 20621 6161 California 11056 2742 Oregon 5058 1587 Washington 4835 1621 Utah 4209 1544 Nevada 6516 2193 West 31674 15848
The @SUMRANGE() function returns the summation of all the values of the specified member (mbrName) across the specified range (rangeList).
@SUMRANGE(mbrName [, rangeList])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the same dimension. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
The following example is based on the Sample Basic database. Assume that the Year dimension contains an additional member, Partial Year. The formula for Partial Year sums the values for New York across the range of Jan through Jun. The calculation script fixes on Sales, so this formula is applied only to Sales values.
FIX(Sales) "Partial Year"=@SUMRANGE("New York",Jan:Jun); ENDFIX This example produces the following report:
Actual New York Colas Sales ===== Jan 678 Feb 645 Mar 675 Apr 712 May 756 Jun 890 Partial Year 4356
The @SYD() function calculates the amount per period that an asset in the current period may be depreciated, across a range of periods. The depreciation method used is sum of the year's digits. More than one asset may be depreciated over the range. The value is depreciated from its entry period to the last period in the range. The resulting value represents the sum of all per-period depreciation values of each asset.
@SYD (costMbr, salvageMbrConst, lifeMbrConst [, rangeList])
costMbr | Single member specification representing an input asset for the current period. |
salvageMbrConst | Single member specification, variable name, or numeric expression, providing a constant numeric value. This value is the value of the asset in the current period after the useful life of the asset. |
lifeMbrConst | Single member specification, variable name, or numeric expression representing the useful life of the asset. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions from the dimension tagged as Time. rangeList represents the range over which the function accepts input and returns depreciation values. If rangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time. |
In this example, the depreciation for each year is calculated by taking into account the initial asset (Asset), the salvage value of the asset (Residual), and the life of the asset (Life).
"SYD Dep"=@SYD(Asset,Residual,Life,FY1991:FY1994,FY1995);
This example produces the following report:
FY1991 FY1992 FY1993 FY1994 FY1995 ====== ====== ====== ====== ====== Asset 9,000 0 1,000 0 0 Residual 750.00 0.00 0.00 0.00 0 Life 5.00 #MISSING 3.00 0.00 0.00 SYD Dep 2750 2200 2150 1433 717
The @TODATE() function converts date strings to numbers that can be used in calculation formulas. @TODATE converts date strings into the number of seconds elapsed since midnight, January 1, 1970.
@TODATE (formatString, dateString)
formatString | The format of the date string, either "mm-dd-yyyy" or "dd-mm-yyyy". |
dateString | The date string. |
The following example is based on the Sample Basic database.
Marketing (IF (@ATTRIBUTEVAL("Intro Date") > @TODATE("mm-dd-yyyy","06-30-1996")) Marketing - (Marketing * .1); ENDIF;);
This formula searches for members with an Intro Date attribute member that is later than 6-30-96 and decreases Marketing for those members by 10 percent. In order to process the formula, Essbase converts the date strings to numbers before it calculates.
This example produces the following report:
Actual Jan Massachusetts Marketing Intro Date_12-10-1996 200-30 9 200-40 9 Intro Date_10-01-1996 400-10 9 400-20 9 Intro Date_07-26-1996 200-20 9 Intro Date_06-26-1996 300-10 9 300-20 9 300-30 9 Intro Date_04-01-1996 100-20 10 100-30 10 Intro Date_03-25-1996 100-10 10 Intro Date_09-27-1995 200-10 10
@ATTRIBUTE
@ATTRIBUTEVAL
@WITHATTR
The @TREND() function calculates future values based on curve-fitting to historical values. The @TREND procedure considers a number of observations; constructs a mathematical model of the process based on these observations (that is, fits a curve); and predicts values for a future observation. You can use weights to assign credibility coefficients to particular observations, report errors of the curve fitting, choose the forecasting method to be used (for example, linear regression), and specify certain data filters.
@TREND (Ylist, [Xlist], [weightList], [errorList],
[XforecastList],
YforecastList, method[, method parameters] [, Xfilter1
[, parameters]] [, XfilterN [, parameters]]
[, Yfilter1 [, parameters]] [, YfilterN [, parameters]])
Ylist | An expression list that contains known observations; for example, sales figures over a period of time. |
Xlist | Optional. An expression list that contains underlying variable values. For example, for each sales figure in Ylist, Xlist may contain a value for associated time periods. If you do not specify Xlist, the default variable values are 1,2,3, and so on, up to the number of values in Ylist. |
weightList | Optional. An expression list that contains weights for the data points
in Ylist, for the linear regression method only. If values in
weightList are #MISSING , the default is 1. Weights
for methods other than linear regression are ignored. Negative weights
are replaced with their absolute values. |
errorList | Optional. Member list that represents the differences between the data points in Ylist and the data points on the line or curve (as specified for method). |
XforecastList | Optional. Expression list that contains the underlying variable values
for which the forecasting is sought. If you do not specify XforecastList,
the values are assumed to be as follows: {(last value in Xlist + 1), (last value in Xlist + 2), ...} up to (last value in Xlist + the number of values in YforecastList) If you forecast consecutively from where Ylist stops, you do not need to specify XforecastList. If you want to move the forecasting period forward, specify the new period with XforecastList. |
YforecastList | A member list into which the forecast values are placed. |
method | A choice among LR (linear regression), SES (single exponential smoothing),
DES (double exponential smoothing), and TES (triple exponential smoothing).
Method parameters must be numeric values, not member names. Method parameters
may be any of the following:
|
Xfilter1 ... XfilterN | Optional. Use one or more of the following filter methods to scale
Xlist:
|
Yfilter1 ... YfilterN | Optional. Use one or more of the following filter methods to scale
Ylist:
|
#MISSING
values during calculation
of the trend.#MISSING
values.
Also, if you specify Xlist, the data must be equidistant, with
the interval (step) being a whole fraction of the period, T (for example,
T/5, T/2). The XforecastList parameters should also contain multiples
of the interval.The following example is based on the Sample Basic database. It forecasts sales data for May through December, based on the trend of the same sales data from January through April. The method used is linear regression with no seasonal adjustment.
Sales(@TREND(Jan:Apr,,,,,May:Dec,LR););
This example produces the following report:
Actual Sales West Colas ===== Jan 2339 Feb 2298 Mar 2313 Apr 2332 May 2319 Jun 2318.4 Jul 2317.8 Aug 2317.2 Sep 2316.6 Oct 2316 Nov 2315.4 Dec 2314.8 Year 27817.2
Click the following links to see the algorithms used for each method:
The @TRUNCATE() function removes the fractional part of expression, returning the integer.
@TRUNCATE (expression)
expression | Single member specification, function, variable name, or other numeric expression, which returns a numeric value. |
In the following example, Total Sales is calculated by (1) taking the sum of the values for Direct Sales and Other Sales and (2) truncating the summed values.
"Total Sales" = @TRUNCATE(@SUM("Direct Sales":"Other Sales"));
This example produces the following report:
Colas New York Actual Jan Feb Mar === === === Direct Sales 678.557 645.874 675.299 Other Sales 411.299 389.554 423.547 Total Sales 1089 1035 1098
The @UDA function returns members based on a common attribute, which you have defined as a user-defined attribute (UDA) on the Essbase server.
@UDA (dimName, uda)
dimName | Name of the dimension with which the uda is associated. |
uda | Name of the user-defined attribute as it appears in the database outline. |
In the Sample Basic database:
@UDA(Market, "New Mkt")
Returns a list of members with the UDA of New Mkt
.
The @VAR() function calculates the variance (difference) between two members. The variance calculation recognizes the difference between accounts that are tagged in the database outline as "Expense" or "No Expense" and calculates the variance accordingly.
@VAR (mbrName1, mbrName2)
mbrName1 and mbrName2 | Members from the same dimension whose variance results are to be calculated. The variance is derived by subtracting mbrName2 values from mbrName1, unless an account is tagged as "Expense", in which case mbrName1 values are subtracted from mbrName2. |
The following example is based on the Sample Basic database. The variance between Actual and Budget is calculated as follows:
Variance = @VAR(Actual,Budget);
In this example Sales is defined as "No Expense", whereas COGS is tagged as "Expense". This example produces the following report:
Year Product Market Sales COGS ===== ==== Actual 400855 179336 Budget 373080 158940 Variance 27775 (20396)
@VARPER
@VARIANCE, which calculates statistical variance
based on a sample of a population
@VARIANCEP, which calculates statistical variance
based on an entire population
The @VARPER() function calculates the percent variance (difference) between two members. The variance calculation recognizes the difference between accounts that are tagged in the database outline as "Expense" or "No Expense" and calculates the variance accordingly.
@VARPER (mbrName1, mbrName2)
mbrName1 and mbrName2 | Members from the same dimension whose variance results are to be calculated. The percent variance is derived by taking the percent variance of mbrName2 values from mbrName1, unless an account is tagged as "Expense", in which case mbrName1 values are taken as a percent variance of mbrName2. |
The following example is based on the Sample Basic database. The percent variance between Actual and Budget is calculated as follows:
Variance = @VARPER(Actual,Budget);
In this example Sales is defined as "No Expense", whereas COGS is tagged as "Expense". This example produces the following report:
Year Product Market Sales COGS ===== ==== Actual 400855 179336 Budget 373080 158940 Variance % 7.4 (12.8)
@VAR
@VARIANCE, which calculates statistical variance
based on a sample of a population
@VARIANCEP, which calculates statistical variance
based on an entire population
The @VARIANCE() function calculates the statistical variance of the specified data set (expList). The calculation is based upon a sample of a population. Variance is a measure of the dispersion of a set of data points around their mean (average) value.
@VARIANCE (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)
SKIPNONE | Includes all cells specified in expList, regardless of their content, during calculation of the variance. |
SKIPMISSING | Excludes all #MISSING values from expList during
calculation of the variance. |
SKIPZERO | Excludes all zero (0) values from expList during calculation of the variance. |
SKIPBOTH | Excludes all zero (0) values and #MISSING values from
expList during calculation of the variance. |
expList | Comma-delimited list of member specifications, variable names, functions, or numeric expressions. expList provides a list of numeric values across which the variance is calculated. |
The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Sales Var. This example uses the @RANGE function to generate expList, and calculates the variance of the sales values for a product family.
FIX (Product) "Sales Var" = @VARIANCE(SKIPBOTH,@RANGE(Sales,@CHILDREN(Product))); ENDFIX
This example produces the following report:
Jan New York Actual Budget ====== ====== Sales Colas 678 640 Root Beer 551 530 Cream Soda 663 510 Fruit Soda 587 620 Diet Drinks #MI #MI Product 2479 2300 Sales Var Product 3687.58 4166.67
The @VARIANCEP() function calculates the statistical variance of the specified data set (expList). The calculation is based upon the entire population. Variance is a measure of the dispersion of a set of data points around their mean (average) value.
@VARIANCEP (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)
SKIPNONE | Includes all cells specified in expList, regardless of their content, during calculation of the variance. |
SKIPMISSING | Excludes all #MISSING values from expList during
calculation of the variance. |
SKIPZERO | Excludes all zero (0) values from expList during calculation of the variance. |
SKIPBOTH | Excludes all zero (0) values and #MISSING values from
expList during calculation of the variance. |
expList | Comma-delimited list of member specifications, variable names, functions, or numeric expressions. expList provides a list of numeric values across which the variance is calculated. |
The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Sales Var. This example uses the @RANGE function to generate expList and calculates the variance of the sales values for a product family.
FIX (Product) "Sales Var" = @VARIANCEP(SKIPBOTH,@RANGE(Sales,@CHILDREN(Product))); ENDFIX
This example produces the following report:
Jan New York Actual Budget ====== ====== Sales Colas 678 640 Root Beer 551 530 Cream Soda 663 510 Fruit Soda 587 620 Diet Drinks #MI #MI Product 2479 2300 Sales Var Product 2765.69 3125
The @WITHATTR() function returns all base members that are associated with an attribute that satisfies the conditions you specify. You can use operators such as >, <, =, and IN to specify conditions that must be met. @WITHATTR can be used as a parameter of another function, where that parameter is a list of members.
@WITHATTR (dimName, "operator", value)
dimName | Single attribute dimension name. |
operator | Operator specification, which must be enclosed in quotation marks (""). |
value | A value that, in combination with the operator, defines the condition that must be met. The value can be an attribute member specification, a constant, or a date-format function (that is, @TODATE). |
@ATTRIBUTE(Bottle) @WITHATTR("Pkg Type","==",Bottle)
However, the following formula can be performed only with @WITHATTR (not with @ATTRIBUTE) because you specify a condition:
@WITHATTR(Ounces,">","16")
The following operators are supported:
> | Greater than |
>= | Greater than or equal to |
< | Less than |
<= | Less than or equal to |
= = | Equal to |
<> or != | Not equal to |
IN | In |
@WITHATTR(Population,"IN",Medium)
returns
the base members that are associated with all attributes under the Medium
parent member in the Population dimension. Text | @WITHATTR(Flavors,"<",Orange) returns base
members with attributes that precede Orange in the alphabet; for example,
Apple, Cranberry, Mango, and Oat, but not Peach or Strawberry. |
Boolean | @WITHATTR(Caffeinated,"<",True) returns
all base members that have Caffeinated set to False (or No). It does
not return base members that do not have Caffeinated set to True
(or Yes) or do not have a Caffeinated attribute at all. The behavior
is similar for a formula like @WITHATTR(Caffeinated,"<>",True) ,
which returns only base members with Caffeinated set to False. |
Date | @WITHATTR("Intro Date","<",@TODATE("mm-dd-yyyy","07-26-1996"))
returns all base members with date attributes that are before
July 26, 1996. |
The following table shows examples, based on the Sample Basic database, for each type of operator:
> | @WITHATTR(Population,">","18000000") | Returns New York, California, and Texas |
>= | @WITHATTR(Population,">=",10000000) where 10,000,000 is not a numeric attribute member, but a constant | Returns New York, Florida, California, Texas, Illinois, and Ohio |
< | @WITHATTR(Ounces,"<","16") | Returns Cola, Diet Cola, Old Fashioned, Sasparilla, and Diet Cream |
<= | @WITHATTR("Intro Date","<=", @TODATE("mm-dd-yyyy", "04-01-1996")) |
Returns Cola, Diet Cola, Caffeine Free Cola, and Old Fashioned |
= = | @WITHATTR("Pkg Type","= =",Can) | Returns Cola, Diet Cola, and Diet Cream |
<> or != | @WITHATTR(Caffeinated,"<>",True) | Returns Caffeine Free Cola, Sasparilla, Birch Beer, Grape, Orange Strawberry |
IN | @WITHATTR("Population","IN",Medium) | Returns Massachusetts, Florida, Illinois, and Ohio |
The following two examples show @WITHATTR used in a calculation script, based on the Sample Basic database:
/* To increase by 10% the price of products that are greater than or equal to 20 ounces */ FIX (@WITHATTR(Ounces,">=","20")) Price = Price * 1.1; ENDFIX
/* To increase by 10% the marketing budget for products brought to market after a certain date */ FIX (@WITHATTR("Intro Date",">", @TODATE("mm-dd-yyyy","06-26-1996"))); Marketing = Marketing * 1.1; ENDFIX
@ATTRIBUTE
@ATTRIBUTEVAL
@TODATE
The @XREF function is a cross-database function that allows a calculation taking place in one Essbase database to incorporate values from a different, possibly remote database.
The following terminology is used to describe the @XREF function:
The @XREF function retrieves values from a data source to be used in a calculation on a data target. @XREF does not impose member and dimension mapping restrictions, which means that the data source and data target outlines can be different.
As arguments, this function takes a location alias, an implied list of members that represents the current point of view, and an optional list of members to qualify the @XREF query on the data source. The second argument (the members making up the current point of view) is implied; that is, these members are not specified as an @XREF parameter. An @XREF query that omits the third argument indicates that a given data point in the data target will be set to the same data point in the data source.
@XREF (locationAlias [, mbrList])
locationAlias | A location alias for the data source. A location alias is a descriptor that identifies the data source. The location alias specifies a server, application, database, username, and password. Location aliases are set by the database administrator at the database level using Application Manager, ESSCMD, or the API. For more information about setting location aliases, see the appropriate documentation. For example, if you want to set location aliases through Application Manager, see the Database Administrator's Guide or the Application Manager Online Help. |
mbrList | Optional. A comma-delimited list of member names that qualify the
@XREF query. The members you specify for mbrList are sent
to the data source in addition to the members in the current point of
view in the data target. The data source then constructs a member combination,
using in order of precedence:
The mbrList parameter (1) modifies the point of view on the data target or (2) defines a specific point of view on the data source. For example, the following formula modifies the point of view on the data target: 1999->Jan->Inventory = @XREF(sourceDB,Dec); If the cube on the data source (sourceDB) contains data only from 1998, this formula sets Inventory for Jan in 1999 to the Inventory value for Dec from 1998. The following formula defines a specific point of view on the data target: Sales->Jan = @XREF(sourceDB,January); Assume that the data target contains the member Jan, while the data source (sourceDB) contains the member January. This formula simply maps the member in the data target (Jan) with its corresponding member in the data source (January), and pulls Sales->January from sourceDB. See Notes for more information about the mbrList parameter. |
West->Inventory = @XREF(SourceDb, California, Oregon);
This formula would return two data values, one for Inventory in California
and one for Inventory in Oregon. However, since the current point of view
calls for only one data cell Inventory->West, Essbase returns an error
message.
West->Inventory = @XREF(SourceDb, @LEVMBRS(Market,0));
West->Inventory = @XREF(SourceDb, Jan:Mar);
RedThings->Sales = @XREF(SourceDb, Red);
RedThings->Sales = @XREF(SourceDb, Red, Average);
For more information on attributes, see the Database Administrator's
Guide.
For this example, consider the following two databases:
Year Qtr1 Qtr2 Measures Sales Units Product 100 100-10 100-20 Market East West Scenario Budget Forecast
Inflation Rates Database
Year Qtr1 Qtr2 Assumptions Inflation Deflation = Inflation * .5 (Dynamic Calc) Country US Canada Europe
The following formula is associated with the Main Database:
Units = Units * @XREF(InflatDB,Inflation,US);
Where InflatDB
is the location alias for the Inflation Rates
Database and Inflation
is the member for which a data value
is retrieved from InflatDB
.
In this example, Essbase calculates the following member combinations:
Units->Qtr1->100-10->East->Budget = Units->Qtr1->100-10->East->Budget
*
Inflation->Qtr1->US
Units->Qtr2->100-10->East->Budget = Units->Qtr2->100-10->East->Budget
*
Inflation->Qtr2->US
and so on.
Copyright 1991-2002 Hyperion Solutions Corporation. All rights reserved.