The RANK function evaluates a set of values and assigns sequential rankings starting at 1.
The RANK function evaluates a set of values and assigns sequential rankings starting at 1.
For example, you can use the RANK function to rank different territories by sales revenue.
RANK(Source values [, Direction] [, Equal value behavior] [, Include value] [, Ranking groups])
Argument | Data type | Description |
Source values | Number, date, or time period | The number, date, or time period to rank. |
Direction (optional) | Keyword | Determines the direction to rank in. The keywords are DESCENDING and ASCENDING. There's more information in the Direction argument keywords section below. |
Equal value behavior (optional) | Keyword | Determines how to rank equal values. The keywords are MINIMUM, MAXIMUM, AVERAGE, and SEQUENTIAL. There's more information in the Equal value behavior argument keywords section below. |
Include value (optional) | Boolean | Determines if a value is ranked. The default value, TRUE, includes a value in the ranking. A value of FALSE omits a value from the ranking and returns a result of NaN (Not a Number). |
Ranking groups (optional) | Number, Boolean, date, time period, list, or text | If provided, the source values are ranked independently for each value in the Ranking groups argument. |
The RANK function returns a number-formatted result.
Keyword | Description |
DESCENDING | The default keyword if you omit the Direction argument. When used, the RANK function assigns the highest source value rank 1, the second highest source value rank 2, and so on. |
ASCENDING | When used, the RANK function assigns the lowest source value rank 1, the second lowest source value rank 2, and so on. |
Keyword | Description |
MINIMUM | The default keyword if you omit the Equal value behavior argument. 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 in the underlying list. |
RANK(Revenue, DESCENDING, MINIMUM, Include in Ranking?, Region)
This formula ranks the values of Revenue line item if the Include in Ranking? line item is TRUE. It ranks values in separate groups determined by Region.
As the formula uses the DESCENDING and MINIMUM keywords:
The Source values argument for the RANK function can be a number-, date-, or time period-formatted line item, property, or expression. However, the function always returns a number-formatted result.
When the RANK function ranks values with the default DESCENDING keyword for the Direction argument, the function ranks the highest value as 1, the second highest as 2, and so on. If you use RANK with:
If the module that contains the Source values argument applies to more than one other dimension (excluding Time and Versions), the ranking applies across all combinations of those dimensions. This includes values that do not currently display on your view of the data.
Consider a module with two line items, Sales and Sales Rank, which apply to Region and Product. There are 100 products sold in 50 regions.
Sales Rank = RANK(Sales)
In this example, the formula returns ranks between 1 and 5,000, ranking each combination of Product and Region across all pages.
You can reference the Users list with the RANK function. However, you can't reference specific users within the Users list as this is production data, which can change and make your formula invalid.
Anaplan imposes an artificial limit of 50 million cells to prevent the ranking of large data sets that would slow down the server. If you use more than 50 million cells 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 non-summarized cells.
As the number of cells you use with the RANK function increases, so does the duration of the calculations.
Infinities and NaN are automatically excluded from the RANK function and return a result of NaN.
You cannot currently use the RANK function in Polaris.
In the Classic Engine, you can.
London | Birmingham | UK | Paris | Lyon | France | Munich | Berlin | Germany | New York | Los Angeles | USA | Total Company | |
Sales | 11,000 | 20,000 | 31,000 | 14,000 | 14,000 | 28,000 | 25,000 | 1,000 | 26,000 | 8,000 | 30,000 | 38,000 | 123,000 |
RANK (Sales) | 6 | 3 | 4 | 4 | 2 | 8 | 7 | 1 |
This example contains an Organization list on columns, where cities are children of regions. There are two line items on rows, one for Sales, and one for the formula that contains the RANK function.
The cities are ranked by their value for the Sales line item. As the parent regions are summaries, they omit from the ranking.
As the formula only contains the Source values argument, the default keywords are used for the optional Direction and Equal value behavior arguments.
The default DESCENDING keyword is used for the Direction argument. This means the city with the largest value for the Sales line item ranks the highest.
The default MINIMUM keyword is used for the Equal value behavior argument. This means that Paris and Lyon, which both have the same value for the Sales line item, are both assigned the lower ranking. In this case, 4.
London | Birmingham | UK | Paris | Lyon | France | Munich | Berlin | Germany | New York | Los Angeles | USA | Total Company | |
Sales | 11,000 | 20,000 | 31,000 | 14,000 | 14,000 | 28,000 | 25,000 | 1,000 | 26,000 | 8,000 | 30,000 | 38,000 | 123,000 |
RANK (Sales, DESCENDING) | 6 | 3 | 4 | 4 | 2 | 8 | 7 | 1 | |||||
RANK (Sales, ASCENDING) | 3 | 6 | 4 | 4 | 7 | 1 | 2 | 8 |
This example shows the behavior of the two keywords for the Direction argument.
The result of the DESCENDING keyword is that the largest value for the Sales line item ranks as 1, through to the smallest value, which ranks as 8.
The result of the ASCENDING keyword is that the smallest number for the Sales line item ranks as 1, through to the largest value, which ranks as 8.
London | Birmingham | UK | Paris | Lyon | France | Munich | Berlin | Germany | New York | Los Angeles | USA | Total Company | |
Sales | 11,000 | 20,000 | 31,000 | 14,000 | 14,000 | 28,000 | 25,000 | 1,000 | 26,000 | 8,000 | 30,000 | 38,000 | 123,000 |
RANK (Sales, DESCENDING, MINIMUM) | 6 | 3 | 4 | 4 | 7 | 1 | 2 | 8 | |||||
RANK (Sales, DESCENDING, MAXIMUM) | 6 | 3 | 5 | 5 | 7 | 1 | 2 | 8 | |||||
RANK (Sales, DESCENDING, AVERAGE) | 6 | 3 | 4.5 | 4.5 | 7 | 1 | 2 | 8 | |||||
RANK (Sales, DESCENDING, SEQUENTIAL) | 6 | 3 | 4 | 5 | 7 | 1 | 2 | 8 |
This example shows the behaviors of the four keywords for the Equal value behavior argument. The values for the Sales line item for Paris and Lyon are both the same, 14,000, so they highlight the different behaviors:
London | Birmingham | UK | Paris | Lyon | France | Munich | Berlin | Germany | New York | Los Angeles | USA | Total Company | |
Sales | 11,000 | 20,000 | 31,000 | 14,000 | 14,000 | 28,000 | 25,000 | 1,000 | 26,000 | 8,000 | 30,000 | 38,000 | 123,000 |
Include in Ranking? | |||||||||||||
RANK(Sales, DESCENDING, MINIMUM, Sales > 10000 AND Sales < 24000) | 4 | 1 | 2 | 2 | NaN | NaN | NaN | NaN | |||||
RANK(Sales, DESCENDING, MINIMUM, Sales > 10000) | 6 | 3 | 4 | 4 | 2 | NaN | NaN | 1 | |||||
RANK(Sales, DESCENDING, MINIMUM, Include in Ranking? = TRUE) | 5 | 2 | 3 | 3 | 1 | NaN | 6 | NaN |
The Include value argument enables you to use a Boolean statement to determine whether a value is ranked. This can be a reference to a Boolean-formatted line item, or a Boolean statement entered directly into the formula.
In the first two formulas, the values to include in the ranking are determined by Boolean statements that specify they must either be:
The third formula contains a reference to the Include in Ranking? Boolean-formatted line item. As a result, it only ranks items in the Organization list, which the Include in Ranking? line item has a value of TRUE for.
London | Birmingham | UK | Paris | Lyon | France | Munich | Berlin | Germany | New York | Los Angeles | USA | Total Company | |
Sales | 11,000 | 20,000 | 31,000 | 14,000 | 14,000 | 28,000 | 25,000 | 1,000 | 26,000 | 8,000 | 30,000 | 38,000 | 123,000 |
Store Type | Ministore | Superstore | Ministore | Ministore | Superstore | Warehouse | Warehouse | Superstore | |||||
Opening Date | 20/3/20/21 | 20/03/2021 | 20/03/2021 | 29/04/2020 | 29/04/2020 | 20/03/2021 | 25/06/2020 | 05/07/2020 | |||||
RANK(Sales, DESCENDING, MINIMUM, Sales > 0, PARENT(ITEM(Organization))) | 2 | 1 | 1 | 1 | 1 | 2 | 2 | 1 | |||||
RANK(Sales, DESCENDING, MINIMUM, Sales > 0, Store Type) | 2 | 3 | 1 | 1 | 2 | 2 | 1 | 1 | |||||
RANK(Sales, DESCENDING, MINIMUM, Sales > 0, Opening Date) | 3 | 1 | 2 | 2 | 1 | 4 | 1 | 1 | |||||
RANK(Sales, DESCENDING, MINIMUM, Sales > 0, YEAR(Opening Date)) | 3 | 1 | 2 | 3 | 2 | 4 | 4 | 1 |
The Ranking groups argument enables you to specify criteria so that data ranks within sub-groups. The four example formulas in the table above demonstrate the following types of data used for the Ranking groups argument:
Store 1 | Store 2 | Store 3 | Store 4 | Store 5 | Store 6 | Store 7 | Store 8 | |
Store Closure Date | 01/01/21 | 02/06/21 | 16/07/21 | 04/03/21 | 14/08/21 | 30/04/21 | 10/09/21 | |
RANK(Store Closure Date, ASCENDING) | 2 | 5 | 6 | 3 | 7 | 1 | 4 | 8 |
Store Open Time Period | Jan 19 | Mar 19 | Jul 19 | Oct 19 | Sep 19 | Jun 19 | Apr 19 | |
| 2 | 3 | 6 | 8 | 7 | 1 | 5 | 4 |
You can also use a date- or time period-formatted value for the Source values argument. If you do this, values rank in chronological order.
If you use the default DESCENDING keyword for the Direction argument, the most recent value ranks as 1, the second most recent value tanks as 2, and so on.
If you use the ASCENDING keyword for the Direction argument, the oldest value ranks as 1, the second oldest value ranks as 2, and so on.
A blank value for a date- or time period-formatted value is ranked as old as possible by the RANK function. As such, in this example, the RANK function ranks the blank value as 8 for the DESCENDING keyword, and 1 for the ASCENDING keyword.
Disclaimer
We update Anapedia content regularly to provide the most up-to-date instructions.