In Tableau, Rank functions refer to different ranking methods used with table calculations, each handling ranking, in particular - ties of values differently.
In this blog I will highlight which each of the different types are, how to use them and a real-world use case:
Competition - RANK(expression, Asc, Desc)
- This is the default ranking function. When values are tied, they receive the same rank, and the next rank number is skipped to account for the tied rows.
- Example: Values 10, 20, 20, 30 would be ranked 1, 2, 2, 4.
- Think Olympics rankings:
Dense - RANK_DENSE(expression, Asc, Desc)
- Similar to Competition, tied values receive the same rank. However, Dense ranking does not skip subsequent rank numbers.
- Example: Values 10, 20, 20, 30 would be ranked 1, 2, 2, 3.
- Ranking students scores example:
Unique - RANK_UNIQUE(expression, Asc, Desc)
- This function assigns a unique rank to every single row, even if the values are identical. This essentially treats each row as distinct for ranking purposes.
- Example: Values 10, 20, 20, 30 would be ranked 1, 2, 3, 4 (the order of the 20s might vary based on the sort order for example alphabetical).
- See in Football league tables, where Chelsea and Liverpool have the exact same GD (GF &GA) and Points - hence Chelsea take 2nd on alphabetical:
Modified Competition - RANK_MODIFIED(expression, Asc, Desc)
- While not explicitly asked for, it's worth noting there's also a "Modified Competition" rank. In this scenario, identical values are assigned an identical rank, but the rank assigned to the tied values is determined differently than standard competition, often by adjusting the position of the ties.
- Example: Values 10, 20, 20, 30 might be ranked 1, 3, 3, 4 or similar, depending on how the "modified" aspect is applied.
- This may seem the same as the Competition (RANK())...
- Tableau includes RANK_MODIFIED() mostly for consistency with SQL functions, where some engines do distinguish between these two more formally.
- In some tools or systems, RANK_MODIFIED() might not skip as many ranks after a tie (e.g., Excel-like behavior), but in Tableau they are functionally near the same
- Better practice would be to use Competition (RANK()) in most cases.
Hope this helps. Let's get ranking!