@CURRMBRRANGE

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])
Parameter Description

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 the @CURRMBRRANGE 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 to be set to FALSE.

See Also