4 Replies Latest reply on Sep 19, 2016 9:17 AM by Ali Hirji

    SUM of Aggregate functions

    Ali Hirji

      Hi all,

       

      I'm relatively new to creating dashboards on Tableau (as it may become apparent with my question) and I'm in desperate need of some help. Firstly, just want to thank Shawn Wallwork for all his help so far - without him, I wouldn't be at the stage I am at the moment. Right, down to the question.

       

      I am in the process of creating a table which holds information on call statistics by day. So, number of calls received (offered), number answered, answer rate, avg talk time, avg hold time, etc... My initial problem was converting time into a format that I was able to create calculations from. For example, a total of 00:02:00 (2 hours talk time) for 2 answered calls would equal an average of 00:01:00 (1 hour) talk time per call. With thanks and all credit to Shawn, *we* were able to break down the data to convert to seconds, divide that by whatever value to calculate the average (in seconds) and then convert it back into a suitable format hh:mm:ss.

       

      My problem now lies when I want to create a calculated field off the back of what I have already calculated. Basically, to sum Avg Talk Time + Avg Hold Time + Avg Wrap Time (highlighted in the screen shot below) to equal the AHT (Average Handling Time). So my desired result is to have a column which sums these values and gives me the result of 11 minutes and 5 seconds (using the highlighted values in the screen shot below as an example). I've tried adding only the seconds to come up with the total seconds but because (i believe) i had to convert data source to seconds in the first place and then to a time format is what is making it fall over/error/fail. No matter what I try (I've also looked through this forum) - I keep getting this error "Argument to SUM (an aggregate function) is already an aggregation, and can not be further aggregated".

       

      I was wondering whether anyone can help me solve this issues or if anyone has a fix for this. I must admit, I'm a little confused why it would be fine to do this on excel (i.e. sum together calculated cells) and not here on Tableau - unless I'm missing something? Any help would be very much appreciated. I have attached an extracted workbook should anyone what to see (or alternative, look at the formula Shawn created to convert date to seconds -> time.). Any help would be very much welcomed!

       

       

      Thank you in anticipation, Ali.

        • 1. Re: SUM of Aggregate functions
          Benjamin Greene

          Try making an AvgHandlingTime_Secs calculated field with the following syntax:

           

          [AvgActiveTime_Secs]+[AvgWrap_Secs]+[AvgHoldTime_Secs]

           

          Then make an AvgHandlingTime_hh:mm:ss calculated field like:

           

          IIF([AvgHandlingTime_Secs] % 60 == 60,0,[AvgHandlingTime_Secs] % 60)

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

          + INT([AvgHandlingTime_Secs]/3600) * 10000

           

          And custom format the number like you have for the other hh:mm:ss fields. That should give you what you want.

           

          Since the active seconds, wrap seconds and hold seconds calculations already are aggregated fields (they have SUM in their calculations) you do not need to (read: are not allowed to) SUM them again in another calculated field. Instead, as I have written above, you can just put the fields directly into the new calculation and it will aggregate them accordingly.

          • 2. Re: SUM of Aggregate functions
            Joe Oppelt

            (Note to self:  Version 9.1 workbook)

             

            See attached.

             

            Took a bit of poking around to see what the author of this sheet did in there.

             

            Your sheet is reducing everything to seconds.  And then does some odd calcs to compute avg time.  And then converts it from (for instance) AvgActiveTime_secs to AvgActiveTime_hh:mm:ss.  And then even at that, the author has put a special format on the end field to display the integer data as HH:MM:SS.

             

            I followed the same steps.  I added the three intermediate values [AvgActiveTime_Secs, etc.] in Calculation 1.  Then I did the conversion that was done for the respective [..._hh:mm:ss] calcs in Calculation 2.  You get a value of 1105.  And then in one more calc, I displayed it with the custom format using 00:00:00 that the author used for the [..._hh:mm:ss] fields.  And that displays as 11:05 (for Calculation 3) instead of 1105 (for calculation 2.)

             

            Odd way to do display these values, but following the original author's template, it works.

             

            You can combine all this into one calc, probably.  Or keep 2 and maintain the pattern used by the original author.  I just expanded it to three to show the progression of steps.

            • 3. Re: SUM of Aggregate functions
              Luciano Vasconcelos

              Hi.

              My version is 9.2. Maybe you can't open.

               

              I just create a calculation like this:

               

              IIF(([AvgActiveTime_Secs] + [AvgWrap_Secs] + [AvgHoldTime_Secs]) % 60 == 60,0,

                  ([AvgActiveTime_Secs] + [AvgWrap_Secs] + [AvgHoldTime_Secs]) % 60)

                  + IIF(INT(([AvgActiveTime_Secs] + [AvgWrap_Secs] + [AvgHoldTime_Secs])/60) %60 == 60,

                      0,

                      INT(([AvgActiveTime_Secs] + [AvgWrap_Secs] + [AvgHoldTime_Secs])/60) %60) * 100

                      + INT(([AvgActiveTime_Secs] + [AvgWrap_Secs] + [AvgHoldTime_Secs])/3600) * 10000

               

              • 4. Re: SUM of Aggregate functions
                Ali Hirji

                Thanks guys (Luciano Vasconcelos, Joe Oppelt and Benjamin Greene)! That worked... I don't know why I didn't try the simplest calculation of just "[AvgActiveTime_Secs]+[AvgWrap_Secs]+[AvgHoldTime_Secs]"...

                 

                Your help/responses are very much appreciated!!!