Rank + Suffix: A Simple Fix

Appending appropriate suffixes to numeric ranks  (e.g., 1, 2, 3 → 1st, 2nd, 3rd) is a problem I've repeatedly encountered in Tableau. What's more, I haven't spotted a definitive online solution. Here, I'll describe one approach comprising a few simple steps.


(1) Rank your data

//[Rank]

    RANK(SUM([Value]))

// This calculation ([Rank]) can assign ranks to a dimension based on the sum of [Value]

(2) Cast the following incantation to return ordinal ranks as strings

//[Rank + Suffix]

IF ENDSWITH(STR([Rank]), '11') THEN STR([Rank])+'th'
    ELSEIF ENDSWITH(STR([Rank]), '12') THEN STR([Rank])+'th'
    ELSEIF ENDSWITH(STR([Rank]), '13') THEN STR([Rank])+'th'
    ELSEIF ENDSWITH(STR([Rank]), '1') THEN STR([Rank])+'st'
    ELSEIF ENDSWITH(STR([Rank]), '2') THEN STR([Rank])+'nd'
    ELSEIF ENDSWITH(STR([Rank]), '3') THEN STR([Rank])+'rd'
    ELSE STR([Rank])+'th'
END

(3) Expand [Rank + Suffix] depending on specific requirements (opt.)

We can modify the [Rank + Suffix] calc. to account for cases where a row's rank is absent e.g., NULL or special character.

//[Rank + Suffix]
IF ENDSWITH(STR([Rank]), '11') THEN STR([Rank])+'th'
    . . .
    . . .
    ELSEIF ENDSWITH(STR([Rank]), '–') THEN (STR([Rank]) 	// or ''
    ELSE STR([Rank])+'th'
END

We can also prevent ranks from appearing until parameter-based filtering has occurred. In other words, aggregated data should not have a rank, but should once it has been disaggregated.

IF [Parameter]="All" THEN "–" ELSE [Rank]
END

Author:
Tom Dobson
Powered by The Information Lab
1st Floor, 25 Watling Street, London, EC4M 9BR
Subscribe
to our Newsletter
Get the lastest news about The Data School and application tips
Subscribe now
© 2025 The Information Lab