By: Eric Parker
Eric Parker 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.
Working with milliseconds in Tableau is tricky. While Tableau does technically handle milliseconds, they aren’t as flexible as other date units like days and hours.
Imagine you are working with a data set tracking swim times and have a table that looks like this:
You’d like to be able to pull these values into Tableau to create a visual that shows how quickly each participant finished the race and label the bars like so:
This is going to take some customization.
First, this process would likely be easier if the data were formatted like this:
Or like this:
However, we’ll handle working with milliseconds in the first format since it is probably the most likely formatting you’ll come across.
First, Tableau’s date formatting can actually handle displaying milliseconds. If I put the Competitor Name field on Rows and the Time field on Text in the Marks card, I end up with something like this.
If no year is specified as part of the date, Tableau defaults the year to 1899. By formatting the Time field as an exact date I get something a little closer to what we’re looking for.
Now, to get the formatting exactly the way we want, we can right-click on the Time field in the Marks card, select Format… and set the formatting like so:
Look at those numbers. It’s working! Unfortunately, this is only half the battle .The second half is to create a field we can use to demonstrate length of duration. This is going to require a multi-step calculation.
The first step will be to duplicate the Time field and convert the duplicate to String field.
Duplication:
Type Conversion:
Now we need to use the SPLIT function to separate Minutes, Seconds and Milliseconds into their own fields.
Here’s what the Milliseconds calculation looks like:
We need to split on the period and we want the last segment of values after the period (in the example above that is 820). The last section “-1” is how we tell Tableau to give us only the values to the right of the period. After we get the correct number we must divide by 1000 (1000 milliseconds in a second) and 60 (60 seconds in a minute). The last step is to convert the remaining number into a number using the INT function.
The calculation for Seconds is similar:
There is one small difference. In this case, we are splitting on the colon and asking for everything that comes after the last colon. that returns “58.820” in the example above. We only need the “58” portion for seconds so we use the left function to grab the first two characters and then divide by 60 (60 seconds in a minute).
That leaves us needing to calculate Minutes:
This time we are splitting on colons but asking for the second segment from the right which, in the above example returns just “02”.
Finally, we combine the three previous calculations into a “Total Time” calculation:
“Total Time” can then be used on length to visualize the swimming results as a bar chart.
Notice the axis is hidden and the custom formatting for milliseconds trick from early is being used to label the bars.
Do you have another trick for dealing with milliseconds in Tableau? Feel free to comment and share below!