@RANK

The @RANK calculation function for Essbase 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.

Syntax

@RANK (rankOrderType, SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, value, XrangeList)

Parameters

rankOrderType

The type of order in which to sort the data set. Options:

• ASCEND Rank values listed in XrangeList in ascending order.
• DESCEND Rank values listed in XrangeList in descending order. This is the default.

SKIPNONE

Includes all cells specified in the data set, regardless of their content, during calculation of the rank.

SKIPMISSING

Excludes all #MISSING values from the data set during calculation of the rank.

SKIPZERO

Excludes all zero (0) values from the data set during calculation of the rank.

SKIPBOTH

Excludes all zero (0) values and #MISSING values from the data set 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.

XrangeList

A list of numeric values across which the rank is calculated. Referred to generically throughout this topic as "the data set."

Can be a valid member name, a comma-delimited list of member names, cross dimensional members, or a return value from a member set function or range function (including @XRANGE).

For more information about XrangeList, see Range List Parameters.

Notes

• After SKIP processing, @RANK sorts the data set in descending order (for example, 15341, 9650, 6556, 4255, 1989) or ascending order (1989, 4255, 6556, 9650, 15341). The rank of a value identifies its position in the sorted data set in descending order (for example, 15341 is ranked 1; 1989 is ranked 5)

• An input value of #MISSING returns #MISSING. #MISSING is also returned if, after SKIP processing, there are no values to compare.

• @RANK assigns the same rank to duplicate values; however, the presence of duplicate values affects the rank numbers. For example, if a list of values contains [2,2,4,5], Essbase first sorts the list in descending order [5,4,2,2] and then ranks it: [5] has a rank of 1, [4] has a rank of 2, and [2] has a rank of 3. In this case, no value has a rank of 4.

• If value is a constant value and that value is not included in the data set (XrangeList), Essbase inserts the constant value in the list and then ranks it accordingly. For example, if a list of values contains [2,4,6,13], and you want to rank (in descending order) a value of [3] in this list, Essbase:

  1. Sorts the list in descending order [13,6,4,2]

  2. Inserts [3] in the list [13,6,4,3,2]

  3. Ranks [3] in the list: in this case, [3] has a rank of 4.

• When you use @RANK in a calculation script, use it within a FIX statement. Although using FIX is not required, it may improve calculation performance.

• When you use @RANK across a large range in a sparse dimension, you may need to increase the size of the calculator cache.

Example

Example 1

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

Example 2

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Expense Rank. Essbase ranks the total expenses values for a set of products in ascending order (the minimum expense is assigned rank 1).

In this example, ASCEND is used to rank the values in ascending order.

"Expense Rank" = @RANK(ASCEND,SKIPBOTH,"Total Expenses",@RANGE("Total Expenses",@LEVMBRS(Product,1)));

This example produces the following report.

                 New York   Actual    Jan
                   Total Expense       Expense Rank
                   =============       ==========
Colas               145                   2
Root Beer           215                   4
Cream Soda          213                   3
Fruit Soda          100                   1
Diet Drinks         #MI                 #MI

Example 3

The following example assumes a Year dimension is added to Sample Basic. It ranks values using cross-dimensional members in the data set.

FIX(Product)
"Sales Rank" = @RANK(SKIPBOTH,Sales,@XRANGE("2011"->"Sep", "2012"->"Mar"));
ENDFIX

The above calculation is performed across the following multidimensional range specified by XrangeList:

2011->Sep
2011->Oct
2011->Nov
2011->Dec
2012->Jan
2012->Feb
2012->Mar

See Also

• @RANGE

• @XRANGE