This week’s tinkering comes from work I was doing for a client in the health services industry. They have a database that tracks all the calls being answered by hundreds of agents in their call center. They wanted a few dashboards that showed number of calls, duration, average duration, number & duration by time of day, etc.
If you have ever worked with time durations you know these can be a challenge because Tableau doesn’t exactly add/subtract and total date-time the way we might expect or need. Let say we have this call:
It should be simple, right? It certainly is in Excel. But in Tableau the problem we have is that the DATEDIFF() function — the one we would use to calculate the difference between dates — can only take one unit of date-time as an argument. For instance:
DATEDIFF(‘minute’, [Start],[End] )
Gives us the number of minutes of a call, but not the seconds. And not in the format we are looking for. So what’s the better solution?
SUM( FLOAT([End] ) )-SUM( FLOAT( [Start]) )
And then set the default formatting for this field to hh:mm:ss. This will produce this:
One note: The duration MUST be less that 24 hours for this to work correctly. Also the totals can’t add to more than 24 hours.
EDIT: Anyone needing to sum time that exceeds 24 hours, can use this formula instead:
//replace [Seconds] with whatever field has the number of seconds in it
IIF([Seconds] % 60 = 60,0,[Seconds] % 60)// seconds
+ IIF(INT([Seconds]/60) %60 = 60, 0, INT([Seconds]/60) %60) * 100 //minutes
+ INT([Seconds]/3600) * 10000 //hours
This comes from Jonathan Drummey’s excellent blog: Formatting Time Durations
Final Note: I don’t have a great memory, but I seem to recall that Jim Wahl was the first to point me toward using the FLOAT() function for adding/subtracting date-time. If it was someone else, please forgive.