3 Replies Latest reply on Oct 3, 2019 6:13 AM by Alan E

    Time Duration when Aggregating Across Multiple Fields

    Kenneth Matthews



      I am having difficulty aggregating time duration across several calculated fields. I am trying to create a field called "Phone Assistance Time," which is dependent on 4 other calculated fields in the workbook. I've done quite a bit of research on this, and have used the technique recommended in the forums and used by Jonathan Drummey found here:

      Formatting Time Durations in Tableau | Drawing with Numbers


      //replace [Seconds] with whatever field has the number of seconds in it

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

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

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

      + INT([Seconds]/3600) * 10000 //hour


      I've also modified it to account for SUM([Seconds]) as noted on the blog. 


      The formula works when viewing the fields independently, however when they are summed together for the "Phone Assistance Time" field (which will be displayed on the dashboard), I get values like 01:84:22 which doesn't really make sense since I want it to be hh:mm:ss. The grand total is not displaying correctly either.


      Note I cannot use the /86400 technique as the total duration will exceed 24 hours.


      The original data source for this file are two custom SQL statements that I have blended together, with LOGIN and EST as the join keys. However in this example I've just used two Excel spreadsheets to mimic the data so I can attach as a packaged workbook that is accessible.


      Thank you! Jonathan Drummey