1 2 Previous Next 20 Replies Latest reply on Jun 6, 2018 4:47 PM by Krishna Dudhela

# Calculating total in hh:mm:ss format

Hi Everyone,

My requirement is to display time duration in hh:mm:ss format.

In my attached workbook, you can see that I am able to calculate hh:mm:ss format. But when I total it(Column wise), it displays in hh:mm:ss format but the minutes and seconds exceeds 60.

Can anyone tell me how can I correct this?

Thanks,

Krish.

• ###### 1. Re: Calculating total in hh:mm:ss format

Have a look here:

• ###### 2. Re: Calculating total in hh:mm:ss format

Hi Deepak,

Thanks for the reply. Could you please attach that workbook as I am unable to find it on the thread for more clear understanding?

• ###### 3. Re: Calculating total in hh:mm:ss format

Unfortunately, I also don't see it there, but you may pl follow steps I included there.

• ###### 4. Re: Calculating total in hh:mm:ss format

Hi ,

If you could take a look at my workbook it would be great.

I am unable to split the data as its a calculated field.

If you could just take a look and give me some hints, it would be great to proceed further.

Thanks.

• ###### 5. Re: Calculating total in hh:mm:ss format

Ok I would check ...Pl wait

• ###### 6. Re: Calculating total in hh:mm:ss format

Hi Krishna,

Please look at Sheet 2 in the attached.  I reviewed some of the calcs being used in Sheet 1 and the results and determined a better more simpler method can be used.  Because the number of seconds exceeds 84,600 (1-day) these calcs will be needed in order to properly sum the amounts.  They're now formatted as dd:hh:mm:ss so you have the  number of business and calendar days (looks like they're all business days btw) in addition to number of hours, minutes, seconds.

All of the new calculations are preceded by a number and a dash so you can see what's been added as new.  Hopefully this helps you out and if so, please mark this response as correct.  Jonathan Drummey has a great site on this particular problem and this is where the calculations came from.  Formatting Time Durations in Tableau | Drawing with Numbers

Thx, Don

1 of 1 people found this helpful
• ###### 7. Re: Calculating total in hh:mm:ss format

Hi Don,

Thanks a ton. I did gain some knowledge with the article that you provided.

But I am getting the following error for the calculation,

So I proceeded with this below formula to calculate seconds. It works without any error.

Is this calculation correct?

• ###### 8. Re: Calculating total in hh:mm:ss format

Hi Krishna,

I would say that your [Time In Seconds] calculation, while it works, doesn't provide you with accurate results.  Please see below screenshot which is giving you negative values when using the original timestamps of Task Start and Task End times.

Is it possible that you can use an extract instead of SQL in order to take use of DATEDIFF?

I'll poke away at what's here...I'm just now realizing that the Start Time and End Time timestamps that I used in my calcs are not the original timestamps but are LOD's to the original timestamps - of which, changes things.

Thanks, Don

• ###### 9. Re: Calculating total in hh:mm:ss format

Yes, I do get negative values.

So, you recommend to take an extract and calculate the data right?

• ###### 10. Re: Calculating total in hh:mm:ss format

Hi Krishna,

OK, I reattached the workbook to this reply.  By using the original Date/Time stamps and moving away from the LOD's (not necessary for this workbook) the numbers changed considerably.

Also, yes, if you can use an extract instead of using DateDiff against SQL, then you should be able to achieve the results in the attached.

Please notice the difference between your existing calc and the DateDiff calc in the screenshot below.

You shouldn't be seeing negative values.  It has to do with the fact that the difference in time (based on Task) can be well over 24 hours in duration; that changes how things need to be calculated. The calculation you're currently using won't rectify that, which is why you're getting odd results.

I modified my calculations to avoid using the LOD's and to use the original date/timestamps. These are the results you should be getting:

Thanks!  Don

1 of 1 people found this helpful
• ###### 11. Re: Calculating total in hh:mm:ss format

Thank you so much Don.

I shifted from live to extract and it solved my problem.

Works perfectly fine now.

• ###### 12. Re: Calculating total in hh:mm:ss format

Great good to know!

It appears its a known issue but no resolution so maybe, if you have to, open up a case/ticket with Tableau:

• ###### 13. Re: Calculating total in hh:mm:ss format

Hi Don,

An extension to this dataset, your help is appreciated.

The data that you see below is the average turnaround time. When I add the grand totals, it gives me the avg of the displayed data. Whereas, I need the sum of the displayed data.

I tried two ways to achieve the sum.

1. Analysis-->Totals-->Total All Using-->Sum

I get this and the values again exceed 60

2. I created a calculated field

Please tell me how should I correct this?

• ###### 14. Re: Calculating total in hh:mm:ss format

Hi Krishna,

I'd be glad to take a look.  Could you reattach your workbook with the above most recent calcs you're using?  Summing time is tricky.

Thx, Don

1 2 Previous Next