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
@XRANGE can be used only in these functions: @AVGRANGE, @SUMRANGE, @MINRANGE, @MINSRANGE, @MAXRANGE, @MAXSRANGE, @STDDEVRANGE, @MOVSUM, @MOVAVG, @MOVMIN, @MOVMAX, @MOVMED, @SPLINE.
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.
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
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
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, "2003"->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