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:

Float-1

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:

Float-4

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.

Advertisements

One comment on “Tinkering with FLOAT() and Durations

  1. Jon Boeckenstedt says:

    I often wonder at the sheer number of small things the engineers at Tableau could do that would make the software so much easier to work with, like this. Big on my list would be putting all the options on the quick filter format into one location….

    Very nice blog, by the way.

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s