Time Values In Hh:mm:ss Format Is Not Recognized Properly When It's More Than 24 Hours

Published: 17 Jul 2020
Last Modified Date: 08 Jul 2022

Question

How to read column values having time expressed in hh:mm:ss format. Example values: 27:22:55, 05:03:00 etc

Environment

Tableau Desktop

Answer

1. Read the values as a String as Date/Time type for such a column will append a date before the time values in columns
2. If you want to calculate average/sum of the duration, first convert the values like 27:22:55 into duration in minutes/hours/seconds using a calculation like below that gives duration in minutes

INT(( SPLIT( [Duration], ":", 1 ) ))*60
+
INT(TRIM( SPLIT( [Duration], ":", 2 ) ))
+
INT(TRIM( SPLIT( [Duration], ":", 3 ) ))/60

3. Use this as a dimension and Table calculations like Window_average and Window_sum.

Example to calculate the average of duration:

if
FIRST()==0 then
WINDOW_AVG(ATTR([Duration in minutes]))
end

User-added image