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
Answer:
Yes, you can display # days, # hours, # minutes, and # seconds. You’ll need to create several calculations.
Step 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])}
Steps 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)
- Hold Days:
(SUM([Hold Time Diff]) – (SUM([Hold Time Diff])%(24*3600)))/(24*3600)
- Hold Hours:
(((SUM([Hold Time Diff])%(24*3600))) – (SUM([Hold Time Diff])%(24*3600))%3600)/3600
- Hold Minutes
(((SUM([Hold Time Diff])%(24*3600))%3600) – ((SUM([Hold Time Diff])%(24*3600))%3600)%60)/60
- Hold Seconds
IF INT(SUM([Hold Time Diff]) %3600 %60) < 10 THEN “0” ELSE “” END + STR(INT(SUM([Hold Time Diff]) %3600 %60))
6 – Combine the above Holds for Days, Hours, Minutes, and Seconds to create a readable Hold Duration field.
- 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”