 Jim Dehner

Tableau How to’s,  Use Cases, and Forums Questions

Hey #DataFam

Let’s Talk

Open for

Time and duration are not the same – Time can be read from a clock – duration is the accumulation on intervals – and although we use a common terminology  – hours – minutes and seconds they are different

This example uses a simple data set consisting of project start and end times by employee Date Functions – (see Date Functions)

The date functions in Tableau are based on truncating the date/time structure shown below If you needed to determine the number of days between the start and end dates you could use

Datediff('day',[Start date],[End date])

and  the value for project A9 would be –  27 but the actual interval is 27 days 1 hour 1 minute  and 32 minutes

Increasing the number of decimal places will not change the result – Datediff truncated the value at the 'day' level So how can we get Tableau to return the actual duration –

#### Determining the duration

The solution is to build the duration from the lowest level (grain) in the data – the example here is in seconds – Yours may differ and you can adjust the model as needed – also we will want to be able to calculate the duration at different levels in the data – this LOD will meet that need

{ INCLUDE [Employee],[Project]:sum( DATEDIFF('second',[Start Date],[End Date])  )}

The LOD will return the difference in seconds – The durations are large and not what you want. They need to be converted to hours, minutes, and seconds

The Duration in Seconds is

sum({ INCLUDE  [Employee],[Project]:sum([datediff at the second LOD]%60)})%60

Again using a LOD to allow summing at different levels in the data –

Note the use of MODULO     –  the function returns the remainder of the measure (here the total seconds)  divided by the value that follows the % sign (60)   –

the LOD will first sum the remaining seconds at the level of the viz – the second use of modulo converts the total to minutes and seconds during the accumulation across the employee level and above.

The Duration in Minutes is

int(sum({ INCLUDE [Employee],[Project]:sum([datediff at the second LOD])/60 }) %60)

The Int() or Floor() will return the integer portion of the calculation rounded down

The Duration in Days is

int(sum({ INCLUDE [Employee],[Project]:sum([datediff at the second LOD])  }) / (60*60*24))

In text table form, the viz is Or in summary #### Format dd:hh:mm:ss

Often users have asked to have a format similar to dd:hh:mm:ss

str([duration days])+":" +str([duration hours])+":" +str([duration minutes])+":" +str([duration – seconds]  )

The formula leverages the integer value in  the LOD (integer) values and converts them to strings

and returns this in summary Time and duration are not the same and it does take a few steps to get the actual duration in a format the user expects – Hope this helps the next time you are faced with a similar problem

The workbook used in these examples can be found at

Enjoy

Jim