Orders a set of values and assigns rankings from 1 to a specified end rank.
RANK(v [,w] [,x] [,y] [,z])
- v: Source
- w: Direction: Ascending or descending order (optional)
- x: Ties: Ranking assignment (optional)
- y: Include: Whether to include the corresponding value in the ranking (optional)
- z: Group: rank the source values independently within each group (optional)
|Input Format||Output Format|
v: Number, date, time period
w: Direction (ASCENDING, DESCENDING)
x: Ties (MINIMUM, MAXIMUM, AVERAGE, SEQUENTIAL)
z: Number, list item, date, Boolean, text, time period
The function uses the following arguments:
- v: Source
- Number: The source values to rank. This may be a reference to a line item or property, or an expression.
- Date. The source dates to rank. This may be a reference to a line item or property, or an expression
- Time period: The source time periods to rank. This may be a reference to a line item or property, or an expression.
- w: Direction: Keyword
- DESCENDING (default): the highest source value is ranked 1, lowest is ranked n
- ASCENDING: the lowest source value is ranked 1, highest is ranked n
- x: Ties: Keyword
- MINIMUM (default): tied values are given the lowest ranking
- MAXIMUM: tied values are given the highest ranking
- AVERAGE: tied values are given the average of the range of applicable rankings
- SEQUENTIAL: tied values are assigned separate rankings, in the order they occur.
- y: Include
- Boolean. Indicates whether to include the corresponding value in the ranking. If supplied, this parameter indicates which values are to be included in the ranking. Excluded values have a ranking of NaN. Defaults to TRUE (include all values)
- z: Group
- Can be number, list, date, Boolean, text, or time period-formatted. If supplied, the source values will be ranked independently within each group, that is, amongst the set of values with the same value in the Group parameter.
The function has the following constraints:
- Formatting - Source is mandatory parameter and must be number, date, or time period-formatted.
- Result must be number-formatted.
- Positive Infinity, Negative Infinity and NaN - Infinities and NaN are automatically excluded from the ranking, and will give a result of NaN.
Other Dimensions -
If the target structure has more than one other dimension (excluding time and versions dimensions), the ranking will be performed across all combinations.
For example if two line items Sales and Result apply to Region and Product, with 100 products sold in 50 regions:
Result = RANK(Sales) will contain the values 1 to 5000, ranking each combination of Product/Region among the full set of values. Typically the target structure will only have one dimension.
- Cell Limit - The RANK function could be slow to evaluate at large data volumes, due to performance characteristics of sorting an arbitrary set that takes O(n * ln(n)). An artificial limit of 10M cells is imposed to prevent ranking of large datasets that would slow down the server: if this is exceeded, the model is rolled back. Note that when used against datasets of more than 1 million cells, any change in source values could result in calculations that take a few seconds.
In this simple example the leaf-level items cities are ranked in order. In the absence of any other variables, the order defaults to descending, with ties set at the minimum value. For example, Paris and Lyon both have the same sales value so the rank is set as 4th equal. Subtotals and totals are excluded from the ranking.
Example of RANK in ascending or descending order
The rank order can be set to ASCENDING or DESCENDING. The default is descending order.
Example of RANK within subtotals or RANK items of the same property
In the examples shown, the ranking takes place within the peer group. This allows for ranking of items of the same parent, property, flag, date or any other data type.
RANK(Sales, DESCENDING, MINIMUM, Sales > 0, PARENT(ITEM(Organization)))
RANK(Sales, DESCENDING, MINIMUM, Sales > 0, Property)
RANK(Sales, DESCENDING, MINIMUM, Flag = TRUE, Flag)
RANK(Sales, DESCENDING, MINIMUM, Sales > 0, Opening Date)
RANK(Sales, DESCENDING, MINIMUM, Sales > 0, YEAR(Opening Date))
Example of RANK, excluding items that don't meet defined criteria
In these examples, items that fail to make the criteria are excluded from the ranking and instead shown as NaN.
RANK(Sales, DESCENDING, MINIMUM, Sales > 10000 AND Sales < 24000)
RANK(Sales, DESCENDING, MINIMUM, Sales > 10000)
Example of different ways of scoring Ties using the RANK function
In this example, the different methods of scoring Ties(x) are shown. Paris and Lyon have the same sales, but their ranking scores can be different depending on the method used for scoring ties.
RANK(Sales, DESCENDING, SEQUENTIAL))
RANK(Sales, DESCENDING, MINIMUM))
RANK(Sales, DESCENDING, MAXIMUM))
RANK(Sales, DESCENDING, AVERAGE))
Remember that if you use the Ties parameter, then you will see tied values grouped at the same ranking with a jump to the next rank number. This example ranks by date value in ascending order and 7 of the 8 values are ranked 1 as the same minimum value and the next rank is therefore 8:
Example with time period values
In this example, we have a module pivoted to show line items on columns. The Month Period line item is time period-formatted for months and an ascending ranking is given for the selected month periods against companies in the organization:
RANK(Month Period, ASCENDING)
Example with date values
In this example, we have a module pivoted to show line items on columns. The Date line item is date period-formatted and an ascending ranking is given for the selected dates against companies in the organization: