TABLEAU: Display days, hours, minutes, and seconds.

Category:
Question ID: 109854
0
0

Hi,

I need to display the number of days, minutes, hours, seconds a product is on hold.  How can I show date difference in written duration using Tableau?

Ex. 0 days 7 hours 48 minutes 10 seconds

Thanks

Marked as spam
Posted by (Questions: 12, Answers: 4)
Asked on October 7, 2020 10:28 am
7 views
Answers (1)
0
Private answer

Yes, you can display # days, # hours, # minutes, and # seconds.  You will need to create several calculations.

Step 1:

  1. Hold Time Diff: this is a level of detail (your group by in SQL)

{FIXED [id], [category], [dates]: SUM(DATEDIFF(‘second’,[Start Date], [End Date])}

Step 2 -6:

Create Hold Days, Hold Hours, Hold Minutes, Hold Seconds, which we will use to display hold days, but more importantly, show hours/mins/sec when the hold is on the same day, which will show "0" under 'Hold Time Days' (see calc below)

  1. Hold Days:

(SUM([Hold Time Diff]) - (SUM([Hold Time Diff])%(24*3600)))/(24*3600)

  1. Hold Hours:

(((SUM([Hold Time Diff])%(24*3600))) - (SUM([Hold Time Diff])%(24*3600))%3600)/3600

  1. Hold Minutes

(((SUM([Hold Time Diff])%(24*3600))%3600) - ((SUM([Hold Time Diff])%(24*3600))%3600)%60)/60

  1. Hold Seconds

IF INT(SUM([Hold Time Diff]) %3600 %60) < 10 THEN "0" ELSE "" END + STR(INT(SUM([Hold Time Diff]) %3600 %60))

Step 6 – Combine the above Holds for Days, Hours, Minutes, and Seconds to create a readable Hold Duration field.

  1. Hold Duration:

IF [Hold Days] > 0 THEN

STR([Hold Days])+" Day"+" "+

STR([Hold Hours])+" Hr"+" "+

STR([Hold Minutes])+" Min"+" "+

STR([Hold Seconds])+" Sec"

ELSE

STR([Hold Hours])+" Hr"+" "+

STR([Hold Minutes])+" Min"+" "+

STR([Hold Seconds])+" Sec"

END

Happy Tableau-ing!

Attachments:
Marked as spam
Posted by (Questions: 4, Answers: 11)
Answered on October 7, 2020 10:33 am