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.
@RANK (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, value, expList)
|SKIPNONE||Includes all cells specified in expList,
regardless of their content, during calculation of the rank.
|SKIPMISSING||Excludes all #MISSING values from expList during
calculation of the rank.
|SKIPZERO||Excludes all zero (0) values from expList during
calculation of the rank.
|SKIPBOTH||Excludes all zero (0) values and #MISSING values from expList 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.
|expList||Comma-delimited list of member specifications, variable names, functions,
or numeric expressions. expList provides
a list of numeric values across which the rank is calculated.
- After SKIP processing, the @RANK function sorts the data set
in descending order (for example, 15341, 9650, 6556, 4255, 1989). The rank
of a value identifies its position in the sorted data set (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.
- The @RANK function 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 [5,4,2,2] and then ranks:  has a rank of 1,  has a rank
of 2, and  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 expList, 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 a value of  in this list, Essbase:
- Sorts the list in descending order [13,6,4,2]
- Inserts  in the list [13,6,4,3,2]
- Ranks  in the list: in this case,  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
- When you use @RANK across a large range in a sparse dimension,
you may need to increase the size of the calculator cache. For more information
on the calculator cache, see the Oracle Essbase Database Administrator’s Guide.
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