2 Replies Latest reply on Jul 21, 2015 6:53 AM by David Murphy

    Duration formatting and sub/grand totals help - dd:hh:mm:ss

    David Murphy

      Hi all,

       

      I'm trying to resolve an issue where I'd like show durations at the sub total and grand total levels as dd:hh:mm:ss (with seconds as the underlying duration value). I've tried using this solution (put together by the wonderful jonathan.drummey) but the equation works out the individual values for each line, and lumps them together e.g. two values of 00:00:31:00, then the sub/grand total would incorrectly be 00:00:62:00 and not 00:01:02:00.

       

      In the tutorial workbook it does make reference to using 3 equations to resolve this but I cannot see how to implement this.

       

      I've made my own example packaged workbook to outline my problem. Click here to view and download it if that helps.

       

      Can anyone please help me resolve this issue, as I'm sure it would help a lot of other people out too

        • 1. Re: Duration formatting and sub/grand totals help - dd:hh:mm:ss
          Jonathan Drummey

          Hi David,

           

          I created a calc called "Duration agg" that is SUM([Duration seconds]) and then created a new duration calc using that new calc instead:

           

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

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

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

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

          + IIF(INT([Duration agg]/3600) % 24 == 0, 0, INT([Duration agg]/3600) % 24) * 10000 //hours

          + INT([Duration agg]/86400) * 1000000 // days

           

          The reason why this works is based on the order of operations. There are three levels of calculation in Tableau - record level, aggregate, and table calc. The duration calc that I'd posted and you used is a record-level calc so it's being computed for each record in the data, and this works just fine in the Individual records view. However when the data is aggregated (such as in a subtotal or grand total) the record level result (that is formated for dd:hh:mm:ss) is being computed and then that result is being aggregated in the subtotal or grand total and returning the nonsensical results.

           

          So instead we aggregate before applying the custom formatting and everything works. Here's a view with the accurate duration agg:

           

          2015-07-21 09_11_38-Tableau - Book1(2).png

           

          Here's a Tableau Public link: https://public.tableau.com/views/durationformattingagg/Individualrecords?:embed=y&:display_count=yes&:showTabs=y

           

          I'll update my post sometime in the next week to help head off this problem!

           

          Jonathan

          • 2. Re: Duration formatting and sub/grand totals help - dd:hh:mm:ss
            David Murphy

            That seems to have done it, thanks Jonathan!