Use the RANKCUMULATE function to cumulate line item values in the order of a rank from a separate line item
RANKCUMULATE(Cumulation values, Ranking values [, Direction] [, Include value] [, Ranking groups])
|Cumulation values (required)||Number||The number to cumulate, based on ranking criteria.|
|Ranking values (required)||Number, date, or time period||The ranking criteria to base cumulation on.|
|Direction (optional)||Keyword||Determines the direction to rank in.|
|Include value (optional)||Boolean||Determines if a value is ranked.|
|Ranking groups (optional)||Number, Boolean, date, or time period||If this argument is provided, the cumulation values are cumulated separately for each value of this argument.|
The RANKCUMULATE function returns a number-formatted result.
The Source values argument can refer to a number-formatted line item, property, or expression.
The Ranking values argument can refer to a number-, date-, or time period-formatted 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 is ranked 2, and so on
- ASCENDING: the lowest source value is ranked 1, the second-lowest source value is ranked 2, and so on
The Include value argument is a Boolean value that determines if the RANKCUMULATE function cumulates the corresponding value. The default behavior is TRUE, which cumulates all values. If FALSE is used, then the cumulation omits the corresponding value, and the cell displays a hyphen.
The Ranking groups argument can be used to split the target values of the RANK function into categories. Each set of values with the same value for the Ranking groups argument is cumulated separately.
The RANKCUMULATE function has the following constraints:
The Cumulation values argument is required, and must be a number-formatted line item, property, or expression.
The Ranking 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.
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).
Time and Versions are excluded from the ranking. If the Cumulation values argument has a Time or Versions dimension, RANKCUMULATE cumulates values independently for each time period or version.
If the target structure has more than one other dimension (excluding the Time and Versions dimensions), the Cumulation values argument cumulates across all combinations where the Ranking values argument is present. If you include the Ranking groups argument, values cumulate across all combinations, but within the specified groups.
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 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 less 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
Infinities and NaN (Not a Number) are automatically excluded from the Ranking values argument and give a result of NaN.
In this example, RANKCUMULATE is used to cumulate sales by representatives. The module is named Sales Rep Annual Costs & Sales. It has the Sales Rep list on the columns dimension, and line items on the rows dimension.
A number-formatted Sales line item is given for the Cumulation values argument, and a number-formatted Years of Service line item as the Ranking values argument. The result, in the default ascending order, is a cumulative aggregation of sales rep annual Sales by years of service:
RANKCUMULATE(Sales, Years of Service)
RANKCUMULATE with Include value argument
A second example modifies the first example to include the optional Include value argument. A Boolean-formatted Include for Ranked Cumulation line item is used for the argument. The result of the addition of this argument is that if the line item does not have a check mark, it is not included in the cumulation.
RANKCUMULATE(Sales, Years of Service, ASCENDING, Include for Ranked Cumulation)
RANKCUMULATE with Ranking groups argument
A third example modifies the second example. The Boolean-formatted Include values argument includes all cumulation values, and the list-formatted Region line item is used for the optional Ranking groups argument. This means the rankings for the cumulation of the Sales values are further separated into the four regions in the list:
RANKCUMULATE(Sales, Years of Service, ASCENDING, Include for Ranked Cumulation, Region)
RANKCUMULATE with multiple dimensions for Cumulation value argument
This example illustrates ranking for cumulative summing where the source line item has two dimensions other than Time and Versions applied. The Ranking groups argument is provided, so the ranking occurs within separate groups across the source dimensions. The Quarter Profit source line item displays figures for Product type by Region. Products are split into two Product Sales Groups across regions that are the Ranking groups argument in a RANKCUMULATE calculation.
RANKCUMULATE(Quarter Profit, Quarter Profit, ASCENDING, TRUE, Product Sales Group)
Note that, in this example, the Group Rank line item uses the RANK function to display group rankings to aid understanding. The rankings displayed in the Group Rank line item are independent of RANKCUMULATE.