Description
Returns the rank of a number in a List or Metric. The rank of a number is its size relative to other values
Syntax
RANK(Source Block [, Group] [, Direction] [, Ties])
-
Source Block: this is the List or a Metric value that you want to rank. It can be number, integer, date, or text formatted. When working with text, it will rank in alphabetical order.
-
Group (optional): Dimension on which ranking will be reset for each item
-
Direction (optional):
ASCfor ascending,DESCfor descending -
Ties (optional):
MINIMUM(by default),MAXIMUM,SEQUENTIAL,AVERAGE
If you need to skip one parameter in the function, such as group for instance, you can use a constant such as 0 , false or "" instead.
Return type
Integer (compatible with number)
Resetting across multiple dimensions
When using the group parameter to reset the values for one dimension, you can just say the name of the dimension that you would like to reset. If you want to perform a reset across multiple dimensions, you must include a unique text or integer property and you must use the & symbol. The unique text property can be something along the lines of the Name or Code property.
For example, if you want to rank a metric and have values reset each month, your formula would look this.
rank(MyMetricByMonthAndTeam, Month)
If you wanted those values to reset for every month and every team, your formula would look like this
rank(MyMetricByMonthAndTeam, Month.Name & Team.Name)
Using a Metric to define the group for resetting
You can also use a metric to define the group for resetting. The metric must be defined by the dimension that you are trying to reset on. For example, if I wanted groups to be reset by Country, my metric defining that group must use Country as a dimension.
Within the Metric defining group, Pigment will group together items that have the same value, any items with different values will create their own groups. You can use any data type to achieve this.
In this example, the Metric Defining Group has a Text data type. France and UK are in one group, Spain, US, Brazil are in a second group and China makes up its own group. Countries are grouped together because the text values are the same.
Examples
| Case | Results | Return Type |
|
| returns the rank of each cell in the Metric called |
|
|
| returns for each month (reset to 1 when switching month), the rank of each cell in the Metric called |
|
RANK('Quantity Sold', 0, desc, sequential) | returns the rank of each cell in the Metric called |
|
Excel equivalent: RANK(number,ref,[order])