-
1. Re: Aggregating HH:MM:SS
Deepak RaiMar 8, 2017 9:23 AM (in response to paolo.raia)
1 of 1 people found this helpfulHi 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
-
2. Re: Aggregating HH:MM:SS
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 RaiMar 8, 2017 12:48 PM (in response to paolo.raia)
1 of 1 people found this helpful -
4. Re: Aggregating HH:MM:SS
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 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 RaiMar 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 Mar 9, 2017 9:04 AM (in response to Deepak Rai)1 of 1 people found this helpfulHi 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.
-
8. Re: Aggregating HH:MM:SS
Deepak RaiMar 9, 2017 9:10 AM (in response to paolo.raia)
1 of 1 people found this helpfulHi 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
-
9. Re: Aggregating HH:MM:SS
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 Mar 10, 2017 6:27 AM (in response to paolo.raia)1 of 1 people found this helpfulNo 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.
-
11. Re: Aggregating HH:MM:SS
Deepak RaiMar 10, 2017 6:33 AM (in response to paolo.raia)
Great Work!!!!