Search

RANK

Orders a set of values and assigns rankings from 1 to a specified end rank.

Syntax

RANK(v [,w] [,x] [,y] [,z]) 

where:

  • 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)

Format

Input Format Output Format

v: Number, date, time period

w: Direction (ASCENDING, DESCENDING)

x: Ties (MINIMUM, MAXIMUM, AVERAGE, SEQUENTIAL)

y: Boolean

z: Number, list item, date, Boolean, text, time period

Number

Arguments

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.

Back to Top

Constraints

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.

Back to Top

Excel equivalent

Back to Top

Example

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.

RANK(Sales)        

Example of RANK in ascending or descending order

The rank order can be set to ASCENDING or DESCENDING. The default is descending order.

RANK(Sales, DESCENDING)        
RANK(Sales, ASCENDING)        

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)        

Remember that if a source value is blank, the blank is ranked first for ascending order and last for descending order. In this example, there is one blank month period and January is therefore ranked at 2.

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:

RANK(Date,ASCENDING,MINIMUM)        

Remember that if a source value is blank, the blank is ranked first for ascending order and last for descending order. In this example, there's one blank month period and 01/01/2014 is therefore ranked at 2.

Back to Top

Similar functions