@XRANGE

Returns the range of members between (and inclusive of) two specified single or cross-dimensional members at the same level.

For example, when you work with the Time and Scenario dimensions, you can use @XRANGE to return a member set combination of Time and Scenario instead of creating a dimension that combines the two (which creates many more individual members than necessary).

@XRANGE is a member set function. Member set functions return a list of members. @XRANGE can appear anywhere in a formula where a range can normally appear.

Syntax

@XRANGE (mbrName1, mbrName2)
Parameter Description

mbrName1

Any valid member name, member combination, or function that returns a single member.

mbrName2

Any valid member name, member combination, or function that returns a single member. If mbrName1 is a cross-dimensional member (such as Actual->Jan), then mbrName2 must be also, and the dimension order must match the order used in mbrName1.

Notes

  • The two arguments to @XRANGE can be either both single members or both cross-dimensional members. For example, @XRANGE(Actual->Jan, Budget) is invalid because a single member and a cross dimensional member are used together. Both @XRANGE(Actual->Jan, Budget->Feb) and @XRANGE(Jan, Mar) are valid.

  • The dimension order of members must match for both arguments. For example, @XRANGE(Actual->Jun, Jul->Budget) is invalid because the two member components are in different orders. @XRANGE(Actual->Jun, Budget->Jul) is valid.

  • Although the syntax is correct, a function such as @XRANGE (Dec, Mar) is meaningless because it results in an empty set.

  • The member components of each argument must be from the same level. For example, @XRANGE(Actual->Jun, Budget->Qtr1) is invalid because Jun and Qtr1 are not from the same level.

Example

The following examples are based on the Sample Basic database.

Example 1

Here is a very simple example using simple members to return the range between Jan and Mar.

@XRANGE(Jan, Mar)

This example returns the following members:

Jan
Feb
Mar

Example 2

Here is a very simple example using cross dimensional members to return the range between Actual, Jan and Budget, Mar:

@XRANGE (Actual->Jan, Budget->Mar)

This example returns the following members:

Actual, Jan
Actual, Feb
Actual, Mar
Actual, Apr
Actual, May
Actual, Jun
Actual, Jul
Actual, Aug
Actual, Sep
Actual, Oct
Actual, Nov
Actual, Dec
Budget, Jan
Budget, Feb
Budget, Mar

Example 3

This example is not based on the Sample Basic database. It is based on database that contains a dimension called Year that contains members for each year, from 2001 to 2003.

The following formula computes the average sales for all months between Mar of 2000 and Jan of 2001.

SalesAvg= @MOVAVG(Sales, 3, @XRANGE("2001"->Mar, "2001"->Jan));

This example returns the following members:

          Colas   New York   Actual              
                     Sales       SalesAvg       
                     =====       ========       
2000  
        Mar            678        678   
        Apr            645        645   
        May            675        666   
        Jun            712        677.3 
        Jul            756        714.3 
        Aug            890        786   
        Sep            924        856.7
        Oct            914        909.3
        Nov            912        916.7
        Dec            723        849.7
2001
        Jan            647        760.7   

See Also