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

ArgumentData typeDescription
Cumulation values (required)NumberThe 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.
DirectionKeyword

Determines the direction to rank in.

The keywords are DESCENDING and ASCENDING. There's more information in the Direction argument keywords section below.

Include valueBoolean

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 groupsNumber, Boolean, date, time period, list, or textIf provided, values are ranked independently for each value of the Ranking groups argument.

The RANKCUMULATE function returns a number.

Direction argument keywords

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

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 further 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

A cell limit of 50 million cells is imposed 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.

If the 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).

Calculation engine functionality differences

You cannot currently use the RANKCUMULATE function in Polaris.

In the Classic Engine, you can.

Excel equivalent

Related Anaplan functions

RANK

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.


BenGrahamRashidLauraRitaDavidMasakiKieranAlisaKarenMartinaOswald
RegionNorthNorthNorthSouthSouthSouthEastEastEastWestWestWest
Sales258,796235,884190,750228,315171,494234,276230,213222,777201,855271,162267,401209,368
Years of service6981211913145151114

Rank by years of service

RANK(Years of Service, DESCENDING)

118105684212162

Rank by years of service within region

RANK(Years of Service, DESCENDING, MINIMUM, TRUE, Region)

312123213132
Include in cumulation?

Cumulated sales by longest tenure

RANKCUMULATE(Sales, Years of Service, DESCENDING)

2,520,4361,836,6142,261,6401,161,8351,333,3292,070,890933,520493,9392,722,291271,1621,600,730703,307

Cumulated sales by tenure for selected employees

RANKCUMULATE(Sales, Years of Service, DESCENDING, Include in cumulation?)

685,430235,884426,634228,315399,8090452,990222,777654,845271,1620480,530

Cumulative sales by tenure for each region

RANKCUMULATE(Sales, Years of Service, DESCENDING, TRUE, Region)

685,430235,884426,634228,315399,809634,085452,990222,777654,845271,162747,931480,530

Disclaimer

We update Anapedia regularly to provide the most up-to-date instructions.