Replacing Special Values with Text in Tableau
A few weeks ago, somebody asked me how to substitute an aggregated zero with a square. I suggested using UTF-8 symbols in a calculated field in order to reach that result. The calculation would look something like this:
IF SUM([Your Field])=0 THEN "■" ELSE STR(SUM([Your Field])) END
It’s not ideal, in my opinion, because you force a numeric value into a string field, but the user was happy, and that was the end of that.
That is… until today.
Somebody asked me how to turn back a field where zeroes were exchanged for a string into simply showing the value zero. Since zero and null sound pretty much the same in German, I misunderstood his intent at first. I suggested he right-click his way into the formatting menu and reset the option “special values”. This field is used to substitute your null values by text of your choosing.
That, of course, wasn’t the correct answer, but my reply pointed him into the right direction. Looking at the options, he realized that he had changed the sheet-specific format for his field. Tableau helps you there by printing the headers in bold if you’ve diverged from the default setting. You can simply right-click on the header and select “clear” in order to reset the format.
But that was how I found out how you can substitute your zeroes in a much easier and far more comfortable way: you can simply add a format after a second semicolon.
What do I mean by that?
When you format your numbers, you can opt to use a custom format. This is helpful if you want to include special symbols or prefixes depending on whether your values are positive or negative. The formatting goes something like this:
[positive values];[negative values]
One of my favourite formats to use for percent difference is this:
▲ +0.0%;▼ -0.0%
But I learned today that there’s not only the semicolon between positive and negative value format. You can add a second semicolon and insert your preferred format for zero values, as well! The result looks like this:
[positive values];[negative values];[zero values]
For my example, I used the median Discount of the Superstore and had it display “no discount” if the value was zero. Since there’s no negative Discount, I simply left the space between the two semicolons blank:
0%;;”no discount”
You can play with this however you like, of course. It might be interesting to change the formatting of a table calculation over difference to the previous period, for example. In my case, I substituted positive and negative and zero values with different texts:
"more than previous period";"less than previous period";"no change"
The benefit here is that this approach allows your field to remain numerical, meaning you can still use it in continuous colour legends and create axes with it. In my first approach where I converted the numerical value into a string using a calculated field, you would need the original field to do all these things and then use the calculation to label everything, which is not very user-friendly and makes for a higher maintenance effort.
I hope you will find this as useful as I do.
This tip was part of the Tableau Speed Tipping session for Beginners hosted by Annabelle Rincon and myself at Tableau Conference 2022. For more tips, see full list.