Ordinal suffixes like the ‘st’ bit of 1st aren’t automatically attached in tableau. This can be quite annoying for formatting and can sometimes make dashboards a bit ugly. Luckily with the help of some logic in a calculation we can automatically attach the correct suffixes onto number values.
The calculation needed is below, the only change that needs to be made is that [N] will need to be replaced with an integer value.
IF (RIGHT(STR([N]),1)=”1″) THEN
IF (RIGHT(STR([N]),2)=”11″) THEN “th” ELSE “st” END
ELSEIF (RIGHT(STR([N]),1)=”2″) THEN
IF (RIGHT(STR([N]),2)=”12″) THEN “th” ELSE “nd” END
ELSEIF (RIGHT(STR([N]),1)=”3″) THEN
IF (RIGHT(STR([N]),2)=”13″) THEN “th” ELSE “rd” END
The calculation in action
I will now give a brief tutorial of where this was applicable in my data and how the calc is used in practice. I will be using the sample superstore data in this example.
Lets say I wanted to rank each state in the dataset based on their sales. I could create a simple table using state on the rows and sum(Sales) on the text shelf.
We can then do a quick table calculation to make this into a ranking and sort the ranks so we see rank 1 at the top and the lowest rank at the bottom.
Now we have our rankings but we would like the appropriate suffixes to be attached to each rank, this is where we create a calculate field using the calculation at the start of the blog. Instead of [N] we just drag the SUM(Sales) calc from the marks card into the calc. The calc will end up looking something like this.
We now have a calculation which adds the appropriate ordinal suffix onto numbers. You can now drag this calc onto the text bit of the marks card and it will add it into the view. It will automatically be placed underneath the data that is already present in the table. To fix this and get the numbers and the suffix alongside each other simply click on the Text bit of the marks card, then click on the 3 little dots on the right hand side, this will bring up a text box with both your calcs in it, simply put them both on the same line.
That’s the final result, hopefully you have found this tip useful and it can make your dashboards a little bit more pretty. If you did, or have found some cool use cases for it then please let me know, I can be found on twitter @thenumberstim.
Cheers for reading (☞ﾟヮﾟ)☞