1. Calculation functions
  2. All Functions
  3. Miscellaneous Functions
  4. RANK

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.

Syntax

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

Arguments

ArgumentData typeDescription
Source valuesNumber, date, or time periodThe 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 textIf provided, the source values are ranked independently for each value in the Ranking groups argument.

The RANK function returns a number-formatted result.

Direction argument keywords

KeywordDescription
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. 

ASCENDINGWhen used, the RANK function assigns the lowest source value rank 1, the second lowest source value rank 2, and so on.

Equal value behavior argument keywords

KeywordDescription
MINIMUM

The default keyword if you omit the Equal value behavior argument.

Gives tied values the lowest ranking of their range.

MAXIMUMGives tied values the highest ranking of their range.
AVERAGEGives tied values the average of the range of applicable rankings.
SEQUENTIALGives tied values separate rankings, in the order they occur.

Syntax example

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 highest values of the Revenue line item are ranked 1.
  • if there are any tied values for the Revenue line item, it assigns them both the lower rank. 

Additional information

Ranking behavior for different data types

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:

  • numbers, the function ranks the largest number the highest.
  • dates, the function ranks the most recent date the highest.
  • time periods, the function ranks the most recent time period the highest.

Ranking behavior across dimensions

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.

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

Cell limit

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.

Positive infinity, negative infinity, and NaN

Infinities and NaN are automatically excluded from the RANK function and return a result of NaN.

Excel equivalent

Related Anaplan functions

RANKCUMULATE

Examples

Example of RANK with only Source values argument


LondonBirminghamUKParisLyonFranceMunichBerlinGermanyNew YorkLos AngelesUSATotal Company
Sales11,00020,00031,00014,00014,00028,00025,0001,00026,0008,00030,00038,000123,000
RANK (Sales)63
44
28
71

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're omitted 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 is ranked 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.

Direction argument examples


LondonBirminghamUKParisLyonFranceMunichBerlinGermanyNew YorkLos AngelesUSATotal Company
Sales11,00020,00031,00014,00014,00028,00025,0001,00026,0008,00030,00038,000123,000
RANK (Sales, DESCENDING)63
44
28
71

RANK (Sales, ASCENDING)36
44
71
28

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 is ranked 1, through to the smallest value, which is ranked 8.

The result of the ASCENDING keyword is that the smallest number for the Sales line item is ranked 1, through to the largest value, which is ranked 8.

Equal value behavior argument examples


LondonBirminghamUKParisLyonFranceMunichBerlinGermanyNew YorkLos AngelesUSATotal Company
Sales11,00020,00031,00014,00014,00028,00025,0001,00026,0008,00030,00038,000123,000
RANK (Sales, DESCENDING, MINIMUM)63
44
71
28

RANK (Sales, DESCENDING, MAXIMUM)63
55
71
28

RANK (Sales, DESCENDING, AVERAGE)63
4.54.5
71
28

RANK (Sales, DESCENDING, SEQUENTIAL)63
45
71
28

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:

  • The MINIMUM keyword assigns both Paris and Lyon the numerically lower rank, 4.
  • The MAXIMUM keyword assigns both Paris and Lyon the numerically higher rank, 5.
  • The AVERAGE keyword assigns both Paris and Lyon the average of their ranks, 4.5.
  • The SEQUENTIAL keyword assigns both Paris and Lyon ranks in the order they display in General Lists.

Include value argument examples


LondonBirminghamUKParisLyonFranceMunichBerlinGermanyNew YorkLos AngelesUSATotal Company
Sales11,00020,00031,00014,00014,00028,00025,0001,00026,0008,00030,00038,000123,000
Include in Ranking?






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

RANK(Sales, DESCENDING, MINIMUM, Sales > 10000)63
44
2NaN
NaN1

RANK(Sales, DESCENDING, MINIMUM, Include in Ranking? = TRUE)52
33
1NaN
6NaN

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:

  • Larger than 10,000 and smaller than 24,000.
  • Larger than 10,000.

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.

Ranking groups argument examples


LondonBirminghamUKParisLyonFranceMunichBerlinGermanyNew YorkLos AngelesUSATotal Company
Sales11,00020,00031,00014,00014,00028,00025,0001,00026,0008,00030,00038,000123,000
Store TypeMinistoreSuperstore
MinistoreMinistore
SuperstoreWarehouse
WarehouseSuperstore

Opening Date20/3/20/2120/03/2021
20/03/202129/04/2020
29/04/202020/03/2021
25/06/202005/07/2020

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

RANK(Sales, DESCENDING, MINIMUM, Sales > 0, Store Type)23
11
22
11

RANK(Sales, DESCENDING, MINIMUM, Sales > 0, Opening Date)31
22
14
11

RANK(Sales, DESCENDING, MINIMUM, Sales > 0, YEAR(Opening Date))31
23
24
41

The Ranking groups argument enables you to specify criteria so that data is ranked within sub-groups. The four example formulas in the table above demonstrate the following types of data used for the Ranking groups argument:

  • The parent item of each territory within the Organization list (retrieved with a combination of the PARENT and ITEM functions). This means that territories are ranked among their peers within each region. For example, London and Birmingham for the UK, Paris and Lyon for France, and so on.
  • A list-formatted Store Type line item that contains the type of store for each territory within the Organization list. Territories are ranked within groups for each store type.
  •  The Opening date for each territory. If territories share their opening date, they're ranked among one another.
  • The Opening date for each territory used with the YEAR function to return the year that date's in. Territories with an opening date in the same year are ranked among one another.

Time period- and date-formatted line item examples


Store 1Store 2Store 3Store 4Store 5Store 6Store 7Store 8
Store Closure Date01/01/2102/06/2116/07/2104/03/2114/08/21
30/04/2110/09/21
RANK(Store Closure Date, ASCENDING)25637148
Store Open Time PeriodJan 19Mar 19Jul 19Oct 19Sep 19
Jun 19Apr 19

RANK(Store Open Time Period, ASCENDING)


23687154

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 is ranked 1, the second most recent value ranked 2, and so on.

If you use the ASCENDING keyword for the Direction argument, the oldest value is ranked 1, the second oldest value ranked 2, and so on.

A blank value for a date- or time period-formatted value is considered to be 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.