By: Eric Parker
Eric lives in Seattle and has been teaching Tableau and Alteryx for 5 years. He's helped thousands of students solve their most pressing problems. If you have a question, feel free to reach out to him directly via email.
Imagine you are working with a data set in Tableau that displays minutes and seconds in decimals. For instance, 1.5 would be reflective of 1 minute and 30 seconds.
You want that 1.5 to show up at 1:30 instead. Here’s how you can write some calculated fields to accomplish that. Unfortunately, Tableau doesn’t a field type called “time” and there is no default conversion process. However, with some creativity, we can still get the desired result.
First, we will create a calculated field to calculate the seconds value.
Here is an explanation for why each of the components of the above calculation are necessary;
STR: The average processing time calculation is numeric. In order to do string manipulation (to split off the last two characters), it must be a string field.
SPLIT: Because we are just calculating seconds, we want to ensure we are splitting the original values so we only get the characters after the period.
LEFT: Tableau doesn’t know at what point it should naturally stop grabbing more characters at the end of a split so if the full value was 2.1243 the split would return 1243 instead of just 12. LEFT allows us to grab only the first two characters.
INT: In order to use the value we just grabbed to calculate seconds it must be a numeric field.
ZN: If the value is 0 return a zero for the calculation.
*.01: Each Value should represent a fraction of a minute so we must multiple the returned value by .01.
*60: By multiplying .5 by 60 we can return 30 seconds.
Now, in order to get the single digit seconds (like 0) to show up as two digits (00), we need a calculation like this;
In essence, this calculation determines if the string is a single digit. If so, it adds a leading 0. If not, it doesn’t.
The Minutes calculation is much simpler.
To calculate the minute value you can split off whatever comes before the period in the Average Processing Time field.
The last calculation combines the minutes and seconds calculations into a single value.
This same approach can be used to calculate hours and minutes in the place of minutes and seconds.