11 Replies Latest reply on Mar 10, 2017 6:33 AM by Deepak Rai

    Aggregating HH:MM:SS

    paolo.raia
    paolo.raia Mar 8, 2017 7:36 AM

      Hi,

       

      I would like to sum/average etc a set of call durations.

       

      For example (hh:mm:ss):-

      Day1 00:04:30

      Day2 01:59:34

       

      The total should be 2 hours, 4 minutes, 4 seconds

       

      Could someone please point me in the right direction.

       

      Many thanks in advance.

      • 866 Views
      • Tags:
        • 1. Re: Aggregating HH:MM:SS
          Deepak Rai
          Deepak Rai Ambassador Mar 8, 2017 9:23 AM (in response to paolo.raia)

          Hi Paolo,

          Please Find attached and see the screenshot. Basically I used the split and then converting all time to seconds and later on converted back to hh:mm:ss and aggregated.

          Hope it Helps!!!

          Thanks

          Deepak

           

           

          1 of 1 people found this helpful
          • 2. Re: Aggregating HH:MM:SS
            paolo.raia
            paolo.raia Mar 8, 2017 12:03 PM (in response to paolo.raia)

            Thanks for replying Deepak. I shall look at this tomorrow when back in work.

             

            One question... Why use % sign I.e. %3600 %60

             

            Many thanks.

            • 3. Re: Aggregating HH:MM:SS
              Deepak Rai
              Deepak Rai Ambassador Mar 8, 2017 12:48 PM (in response to paolo.raia)

              Hi Paolo,

              Pl see below.

              Please Mark it Helpful and CORRECT to close Thread.

              Thanks

              Deepak

              1 of 1 people found this helpful
              • 4. Re: Aggregating HH:MM:SS
                paolo.raia
                paolo.raia Mar 9, 2017 2:01 AM (in response to Deepak Rai)

                Hi Deepak,

                 

                Can you please explain how you make the following calculations...

                [Data - Split 2 - Split 1]

                [Data - Split 2 - Split 2]

                [Data - Split 2 - Split 3]

                 

                Many thanks.

                • 5. Re: Aggregating HH:MM:SS
                  paolo.raia
                  paolo.raia Mar 9, 2017 5:55 AM (in response to paolo.raia)

                  Hi Deepak,

                   

                  I actually got this to work, so many thanks for your help with this.

                   

                  Kind regards.

                  • 6. Re: Aggregating HH:MM:SS
                    Deepak Rai
                    Deepak Rai Ambassador Mar 9, 2017 7:30 AM (in response to paolo.raia)

                    Hi Paolo,

                    Glad to know that. Firt you split Day1 and Time based upon the blank space, and then you split Time based upon colon(:).

                    That's it

                    Thanks

                    Deepak

                    • 7. Re: Aggregating HH:MM:SS
                      paolo.raia
                      paolo.raia Mar 9, 2017 9:04 AM (in response to Deepak Rai)

                      Hi Deepak,

                       

                      Sorry, but can I ask you one further question please.  I would like to get an average of the total amount of hours.

                       

                      So, for example, I currently have this calculation (which is your example) and works really well by summing all the hours over a period of time:-

                       

                      STR(INT(SUM([Total Call Duration_SplitConvSeconds])/3600))

                      + ":" +

                      IF INT(SUM([Total Call Duration_SplitConvSeconds])%3600/60)

                      < 10 THEN "0" ELSE "" END + STR(INT(SUM([Total Call Duration_SplitConvSeconds])%3600/60))

                      + ":" +

                      IF INT(SUM([Total Call Duration_SplitConvSeconds])%3600%60)

                      <10 THEN "0" ELSE "" END + STR(INT(SUM([Total Call Duration_SplitConvSeconds])%3600%60))

                       

                      I want to be able to get his figure and divide it by the total number of calls to get an average.

                       

                      For example:

                      4:19:01 and divide this by 29 which is the total number of calls.

                       

                      I have been trying this, but it's not really working:-

                       

                      STR(INT(SUM(([Total Call Duration_SplitConvSeconds])/[# Chats])/3600)) ... and so on down the calculation, but it doesn't seem to work.

                       

                      Many thanks.

                      1 of 1 people found this helpful
                      • 8. Re: Aggregating HH:MM:SS
                        Deepak Rai
                        Deepak Rai Ambassador Mar 9, 2017 9:10 AM (in response to paolo.raia)

                        Hi Paolo,

                        • Use the Calculations I sent to you to convert your aggregated time into seconds to work at minimum level.
                        • Divide those seconds by 29 to get Average number of Seconds and Round them off using ROUND function
                        • Like if u have 3600 seconds, divide by 29 like this ROUND(3600/29). suppose it is calculation 3
                        • Now you got average Seconds
                        • Convert them back toHH:MM:SS using formula I sent u

                        Hope it Helps!!!

                        Thanks

                        Deepak

                        1 of 1 people found this helpful
                        • 9. Re: Aggregating HH:MM:SS
                          paolo.raia
                          paolo.raia Mar 10, 2017 1:56 AM (in response to Deepak Rai)

                          Hi Deepak,

                           

                          I've got this working ..... but not quite!

                           

                          The graphic on the left works correctly when I manually enter the number 4.  However, if I add the actual field name called

                          # Chat - which is a whole number data type - (right graphic), I get the "cannot be aggregated" message:-

                          I have tried adding SUM and STR and all sorts, but cannot get this to work.

                           

                          Apologies for the persistent questions.

                           

                          Kind regards.

                          • 10. Re: Aggregating HH:MM:SS
                            paolo.raia
                            paolo.raia Mar 10, 2017 6:27 AM (in response to paolo.raia)

                            No worries, I now have this working.  I just had to add SUM to make it work:-

                             

                            SUM(((INT(SPLIT([TOTAL]..........)))/[# Chats]

                             

                            Then take the SUM away from the second calculation.

                             

                            Many thanks again.

                            1 of 1 people found this helpful
                            • 11. Re: Aggregating HH:MM:SS
                              Deepak Rai
                              Deepak Rai Ambassador Mar 10, 2017 6:33 AM (in response to paolo.raia)

                              Great Work!!!!