This is my 4th blog post on Tableau tips and to celebrate, I am going to share how to get that "th" into "4th" on your mark Labels in Tableau!
So here is the scenario; you have a field that is counted in your view for a label or tool tip text that you want to express in a complete sentence. For this example I will be referencing a piece I recently published on Tableau Public that shows the lifespan of a Giant Sequoia compared myself, and my descendants.
The [My Descendants] field is a value from 1 to 98 noting the number of generations that have passed.
I could code in an IF THEN statement for each one, but after making this viz, I realized time is precious! Luckily the ENDSWITH() function in Tableau offers a short cut.
Here is the same calculated field for your copying-and-pasting pleasure:
if STR([My Descendants]) = "0" then ""
ELSEIF (ENDSWITH(STR([My Descendants]), "0")
and STR([My Descendants]) <> "0") then "th"
ELSEIF (ENDSWITH(STR([My Descendants]), "1")
and STR([My Descendants]) <> "11") then "st"
ELSEIF ENDSWITH(STR([My Descendants]), "11") then "th"
ELSEIF ENDSWITH(STR([My Descendants]), "2") then "nd"
ELSEIF ENDSWITH(STR([My Descendants]), "3") then "rd"
ELSEIF ENDSWITH(STR([My Descendants]), "4") then "th"
ELSEIF ENDSWITH(STR([My Descendants]), "5") then "th"
ELSEIF ENDSWITH(STR([My Descendants]), "6") then "th"
ELSEIF ENDSWITH(STR([My Descendants]), "7") then "th"
ELSEIF ENDSWITH(STR([My Descendants]), "8") then "th"
ELSEIF ENDSWITH(STR([My Descendants]), "9") then "th"
else null end
The first line takes out the "0" generation. In this viz, that only refers to the tree and myself so a label is not needed. This series addresses the 1,123,581,321"st" record the same as the 1"st" record. The other exception is 11, which ends in a 1 but I want "th" instead of "st" like the others. For these, just create another set of criteria wrapped in ()'s excluding 11 from the "st" rule, and be sure to included another line just for 11.
For the rest if the ELSEIF's, the calc looks for the last digit in the generation count, and then adds the desired letter combination so the text.
In the tool tip, I added the generation count value and this text right next to each other to create the final effect.
Hope this helps you out and please reach out here or via Twitter to @dataNOTdoctrine with any comments, questions, or requests for future topics!