RANK

Use the RANK function to evaluate a set of values and assign sequential rankings starting at 1.

Syntax

RANK(Source values [, Direction] [, Equal value behavior] [, Include value] [, Ranking groups]) 

Returns

The RANK function returns a number-formatted result.

Arguments

The Source values argument can refer to a line item, property, or expression.

The keywords for the Direction argument are:

• DESCENDING (default): the highest source value is ranked 1, the second highest source value ranked 2, and so on
• ASCENDING: the lowest source value is ranked 1, the second lowest source value ranked 2, and so on

The keywords for the Equal value behavior argument are:

• MINIMUM (default): gives tied values the lowest ranking of their range
• MAXIMUM: gives tied values the highest ranking of their range
• AVERAGE: gives tied values the average of the range of applicable rankings
• SEQUENTIAL: gives tied values separate rankings, in the order they occur

The Include value argument is a Boolean value that determines if the RANK function ranks the corresponding value. The default behavior is TRUE, which ranks all values. If FALSE is used, the RANK function returns a result of NaN (Not a Number).

The Ranking groups argument can be used to split the target values of the RANK function into several categories. Each set of values with the same value for the Ranking groups argument is ranked individually.

Additional information

Ranking behavior across dimensions

If the target module contains more than one other dimension (excluding Time and Versions dimensions), the ranking is performed across all combinations. For example, picture a module with two line items, Sales and Result, which apply to Region and Product, with 100 products sold in 50 regions. In this case, the formula

Result = RANK(Sales)

will contain the values 1 to 5,000, ranking each combination of Product and Region.

Time and Versions are not included in the ranking. If the line item used for the Source value argument has a Time or Versions dimension, the RANK function ranks it independently for each time period or version.

Use RANK with the Users list

You can reference the Users list with the RANK function. However, you cannot reference specific users within the Users list as this is production data, which can change and make your formula invalid.

Constraints

The RANK function has the following constraints:

Formatting Constraints

The Source values argument is required and must be a number, date-, or time period-formatted line item, property, or expression.

The result of the function must be number-formatted.

Cell Limit

An artificial limit is imposed to prevent ranking of large data sets that would slow down the server. This limit is set at 50 million cells. If more than 50 million cells are used with the RANK function, the model rolls back and a notification displays.

The 50 million cell limit does not account for summarized values or the Time and Versions lists. This means you can use the RANK function with a line item with a Cell Count of greater than 50 million cells if there are less than 50 million nonsummarized cells.

As the number of cells you use with the RANK function increases, so does the duration of the calculations.

Positive Infinity, Negative Infinity, and NaN

Infinities and NaN (Not a Number) are automatically excluded from the ranking and give a result of NaN.

Excel equivalent

• RANK

Examples

In this example the leaf-level items cities are ranked in order. In the absence of the optional arguments, the Direction argument defaults to descending, and the Equal value behavior argument to minimum value. For example, Paris and Lyon both have the same sales value so the rank is set as 4. Subtotals and totals are excluded from the ranking.

RANK(Sales)        

RANK with Direction argument

The rank order can be set to ASCENDING or DESCENDING. The default is descending order.

RANK(Sales, DESCENDING)        

RANK(Sales, ASCENDING)        

RANK with Ranking groups argument

In the examples shown, the ranking takes place within the peer group. This allows for ranking of items of the same parent, property, flag, date or any other data type.

RANK(Sales, DESCENDING, MINIMUM, Sales > 0, PARENT(ITEM(Organization)))        

RANK(Sales, DESCENDING, MINIMUM, Sales > 0, Property)        

RANK(Sales, DESCENDING, MINIMUM, Flag = TRUE, Flag)        

RANK(Sales, DESCENDING, MINIMUM, Sales > 0, Opening Date)        

RANK(Sales, DESCENDING, MINIMUM, Sales > 0, YEAR(Opening Date))        

RANK by Boolean criteria

In these examples, items that fail to make the criteria are excluded from the ranking and instead shown as NaN (Not a Number).

RANK(Sales, DESCENDING, MINIMUM, Sales > 10000 AND Sales < 24000)

RANK(Sales, DESCENDING, MINIMUM, Sales > 10000)

RANK with different behaviors for equal values

In this example, the different options for the Equal value behavior argument are shown. Paris and Lyon have the same sales, but their ranking scores can be different depending on the method used for scoring ties.

RANK(Sales, DESCENDING, SEQUENTIAL))        

RANK(Sales, DESCENDING, MINIMUM))        

RANK(Sales, DESCENDING, MAXIMUM))        

RANK(Sales, DESCENDING, AVERAGE))        

If you use the Equal value behavior argument, then you see equal values grouped at the same ranking with a jump to the next rank number. The example below ranks by date value in ascending order. As 7 of the 8 date values are the same, they are ranked 1, and the next rank is therefore 8.

RANK with time as a line item

In this example, we have a module pivoted to show line items on columns. The Month Period line item is time period-formatted for months and an ascending ranking is given for the selected month periods against companies in the organization:

RANK(Month Period, ASCENDING)        

Example with date values

In this example, we have a module pivoted to show line items on columns. The Date line item is date-formatted and an ascending ranking is given for the selected dates against companies in the organization:

RANK(Date,ASCENDING,MINIMUM)        

If a source value is blank, the blank is ranked first for ascending order and last for descending order. This example contains one blank month, so the date 01/01/2014 is ranked 2.

Similar functions

• RANKCUMULATE