5 Replies Latest reply on Aug 31, 2018 12:34 AM by Mahfooj Khan

# How to include an aggregated date measure in the format hh:mm:ss

I have a table that sums or averages a few different measure and I have been trying to adjust two of those to display in hh:mm:ss format. Currently they are average minutes displayed as decimals, example: 1.75 which equals 1 minute and 45 seconds. There are many resources in the forum that explain how to convert the data to hh:mm:ss. I have been able to display the values by "pulling out" the seconds, minutes, and hours and then putting them back together in the format that I need. I run into a problem when I try to include the hh:mm:ss formatted measure into the table. Tableau will not allow me to drop it into the measure values shelf. Below I have screen grabs of the table w/ the original values and a screen grab of the correctly formatted data. I would like to be able to put the dates in wide form right under the "Abandon Calls" row.  Any help would be appreciated!

• ###### 1. Re: How to include an aggregated date measure in the format hh:mm:ss

One Suggestion, An Attached workbook would get you help faster.

Thanks

Deepak

• ###### 2. Re: How to include an aggregated date measure in the format hh:mm:ss

HI Pablo,

Whatever the field you have 1.75*60

Right Click on the field in Measures --> Default Properties --> Number Format --> Custom --> hh:mm:ss

It will give the required O/P

Hope this helps

Kindly mark this answer as correct and helpful so that it will help others

BR,

NB

• ###### 3. Re: How to include an aggregated date measure in the format hh:mm:ss

Hi,

Find my approach below,

First you needs to convert your avg. minutes to seconds. For that you can use below logic

[Avg. Minutes]*60

Now create below calculated field to get the hh:mm:ss from the [Seconds] field

IF (INT(SUM([Seconds])%86400/3600))

< 10 THEN "0" ELSE "" END + STR(INT(SUM([Seconds])%86400/3600))

+ ":" +

IF INT(SUM([Seconds])%3600/60)

< 10 THEN "0" ELSE "" END + STR(INT(SUM([Seconds])%3600/60))

+ ":" +

IF INT(SUM([Seconds]) %3600 %60)

< 10 THEN "0" ELSE "" END + STR(INT(SUM([Seconds]) %3600 %60))

Let us know if this help.

Mahfooj

• ###### 4. Re: How to include an aggregated date measure in the format hh:mm:ss

Hi All,

Thanks for all of the replies! Naveen, I tried your method, while the formatting did change the o/p to the format I need the data was not correct. Mahfooj, I think that what I have been doing is pretty similar to your suggestion. My pain point occurs when I try to add the calculated field to the table. I think since it's formatted as a string, Tableau will not let me drop it in the Measure Values shelf. I'll attach a workbook like Deepak mentioned.

Thanks!

• ###### 5. Re: How to include an aggregated date measure in the format hh:mm:ss

Hi,

Try this,

//use a custom number format of 00:00:00 (drop the first 0 to get rid of leading 0's for hours)

IIF([IVR Seconds] % 60 == 60,0,[IVR Seconds] % 60)// seconds

+ IIF(INT([IVR Seconds]/60) %60 == 60, 0, INT([IVR Seconds]/60) %60) * 100 //minutes

+ INT([IVR Seconds]/3600) * 10000 //hours

Set the default format like this

Use custom

Follow this wonderful blog post for more details regarding formatting time duration

http://drawingwithnumbers.artisart.org/formatting-time-durations/

Hope this help.

Mahfooj

1 of 1 people found this helpful