@CURRMBRRANGE
The @CURRMBRRANGE calculation function for Essbase generates a member list that is based on the relative position of the current member being calculated.
Syntax
@CURRMBRRANGE (dimName, {GEN|LEV}, genLevNum, [startOffset], [endOffset])
Parameters
- 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.
-
A null value returns the first member of the specified genLevNum.
-
An integer value returns the member name relative to the current member being calculated.
-
A negative value specifies a member prior to the current member being calculated in the dimension.
-
A value of 0 returns the name of the member currently being calculated.
-
A positive value specifies a member after the current member being calculated in the dimension.
-
- endOffset
-
Defines the last member in the range to be returned.
-
A null value returns the last member of the specified genLevNum.
-
An integer value returns the member name relative to the current member being calculated.
-
A negative value specifies a member prior to the current member being calculated in the dimension.
-
A value of 0 returns the name of the member currently being calculated.
-
A positive value specifies a member after the current member being calculated in the dimension.
-
Notes
-
You cannot use this function in a FIX statement.
-
The first three parameters of this function (dimName,{GEN|LEV},genLevNum) provide a member range list. The startOffset and endOffset parameters create a subset of this list. For example, consider the following syntax in the context of the Sample Basic database:
@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.
-
Currently, this function can be used only within range and financial functions, such as @AVGRANGE, @MAXRANGE, @COMPOUND, and @SHIFT.
Example
Example 1
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
Example 3
Consider the following formula:
@CURRMBRRANGE(Year,LEV,@CURLEV("Year"),-1,1)
The full range list contains the members of the Year dimension at a particular level. The level is determined by taking the level of the current member being calculated. For example, if the current member being calculated is Jan, the full range list contains all level 0 members of Year dimension (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec). The startOffset and endOffset parameters reduce this list to (Jan, Feb). As there is no member prior to Jan in the full range list, only two members are returned: Jan and Feb. If the current member being calculated is Feb, the subset list includes three members: Jan, Feb, Mar.
Note:
The usage demonstrated by this example would require RTDEPCALCOPTIMIZE configuration to be set to FALSE.