The RANKCUMULATE function ranks values and then cumulates values in order of the ranking. It can perform ranking separately across different groups.
For example, you can use the RANKCUMULATE function to cumulatively sum employees sales revenue in order of their length of service. There is an example of this in the Examples section.
Syntax
RANKCUMULATE(Cumulation values, Ranking values [, Direction] [, Include value] [, Ranking groups])
Arguments
| Argument | Data type | Description |
| Cumulation values (required) | Number, line item, property, or expression. | The number to cumulate, based on ranking criteria. |
| Ranking values (required) | Number, date, or time period Can be a line item, property, or expression. | The ranking criteria to perform cumulation based on. |
| Direction | Keyword | Determines the direction to rank in. The keywords are DESCENDING and ASCENDING. There's more information in the Direction argument keywords section below. |
| Include value | 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 0. |
| Ranking groups | Number, Boolean, date, time period, list. Text is supported in Classic only. | If provided, values are ranked independently for each value of the Ranking groups argument. |
The RANKCUMULATE function returns a number.
Direction argument keywords
| Keyword | Description |
| DESCENDING | When used, the RANKCUMULATE function assigns the highest source value rank 1, the second highest source value rank 2, and so on. |
| ASCENDING | The default keyword if you omit the Direction argument. When used, the RANKCUMULATE function assigns the lowest source value rank 1, the second lowest source value rank 2, and so on. |
Calculation engine functionality differences
In Polaris, RANKCUMULATE cannot be used when the target is dimensioned by a line item subset, or the function makes a reference to a line item subset.
In Polaris you do not have a cell limit. In Classic, it is 50 million cells.
Polaris does not support infinities, in Classic it does. In Classic, if a cumulation source contains an Infinity, then the result from then on until the end of the cumulation is that infinity. However, if an opposite Infinity follows it, the result becomes NaN (Not a Number). In Polaris it will return NaN instead of Infinity.
In Polaris, blank is unordered , so it is unrankable. For RANKCUMULATE, if the ranking value is blank the the function returns zero.
In Polaris, the ranking values can be the BLANK literal, this is, RANKCUMULATE(1, BLANK) is valid (although the function will always return zero in this case).
Syntax example
RANKCUMULATE(Revenue, Transaction Date, DESCENDING, Eligible transaction?, Region)
Additional information
Ranking behavior for different data types
The Ranking values argument for the RANKCUMULATE function can be a number, date, or time period type line item, property, or expression. However, the function always returns a number.
When the RANKCUMULATE function ranks values with the default ASCENDING keyword for the Direction argument, the function ranks the lowest value as 1, the second lowest as 2, and so on. If you use RANKCUMULATE with:
- Numbers, the function ranks the largest number the highest.
- Dates, the function ranks the date furthest in the future the highest.
- Time periods, the function ranks the time period furthest in the future the highest.
Equal ranking value behavior
If two values of the Cumulation values argument share the same ranking for the Ranking values argument, ranking follows the order of any associated list items within General Lists.
Use RANKCUMULATE with the Users list
You can reference the Users list with the RANKCUMULATE 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
In the Classic calculation engine, Anaplan imposes a cell limit of 50 million cells to prevent ranking of large data sets that would slow down the server. If more than 50 million cells are used with the RANKCUMULATE function, the model is rolled 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 RANKCUMULATE function with a line item with a Cell Count of greater than 50 million cells if there are fewer than 50 million nonsummarized cells.
As the number of cells you use with the RANKCUMULATE function increases, so does the duration of the calculation.
Positive Infinity, Negative Infinity, and NaN
If you use positive infinity, negative infinity, or NaN (Not a Number) for the Ranking values argument, the RANKCUMULATE function returns 0.
Cumulation Source Constraints
If your cumulation source is a large data set, the addition of numbers with a large number of decimal places can result in floating point error for the least significant digits.
Excel equivalent
Examples
General examples
In this example, a module that contains the Salespersons list is on columns, and a number of line items is on rows.
The example uses RANKCUMULATE to cumulatively sum sales in the order of each salesperson's length of service. Further iterative formulas use the Include value and Ranking groups arguments to:
- Determine which salesperson's sales to include in the cumulative ranking.
- Further break down the cumulative ranking by region.
Two line items use the RANK function to help you identify the order that RANKCUMULATE cumulates values in.
| Ben | Graham | Rashid | Laura | Rita | David | Masaki | Kieran | Alisa | Karen | Martina | Oswald | |
| Region | North | North | North | South | South | South | East | East | East | West | West | West |
| Sales | 258,796 | 235,884 | 190,750 | 228,315 | 171,494 | 234,276 | 230,213 | 222,777 | 201,855 | 271,162 | 267,401 | 209,368 |
| Years of service | 6 | 9 | 8 | 12 | 11 | 9 | 13 | 14 | 5 | 15 | 11 | 14 |
Rank by years of service
| 11 | 8 | 10 | 5 | 6 | 8 | 4 | 2 | 12 | 1 | 6 | 2 |
| Include in cumulation? | ||||||||||||
Cumulated sales by longest tenure
| 2,520,436 | 1,836,614 | 2,261,640 | 1,161,835 | 1,333,329 | 2,070,890 | 933,520 | 493,939 | 2,722,291 | 271,162 | 1,600,730 | 703,307 |
Cumulated sales by tenure for selected employees
| 685,430 | 235,884 | 426,634 | 228,315 | 399,809 | 0 | 452,990 | 222,777 | 654,845 | 271,162 | 0 | 480,530 |
Cumulative sales by tenure for each region RANKCUMULATE | 685,430 | 235,884 | 426,634 | 228,315 | 399,809 | 634,085 | 452,990 | 222,777 | 654,845 | 271,162 | 747,931 | 480,530 |
