Tinkering with SUM(), Durations & Grand Totals

In my last post I mentioned Jonathan Drummey’s math formula for displaying duration in hh:mm:ss format. Here it is again:

//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 works well until you turn on the Grand Totals. Then everything falls apart, or at least GT sum to incorrect values. I tinkered with it a bit, but eventually I reached out to Jonathan, and as always he sent me back the solution. Instead of summing the entire calculation, we need to sum each individual part. Here is the new formula Jonathan sent me:

//replace [Seconds] with whatever field has the number of seconds in it
IIF(SUM([Seconds]) % 60 = 60,0,SUM([Seconds]) % 60)// seconds
+ IIF(INT(SUM([Seconds])/60) %60 = 60, 0, (INT(SUM([Seconds])/60)) %60) * 100 //minutes
+ INT(SUM([Seconds])/3600) * 10000 //hours

Jonathan thanks again for the help.

Tinkering with FLOAT() and Durations

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.