Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Below is my dummy data,
my requirement is to calculate
Ticket number: Modified Group name: and Total time spent by each group on per ticket.
In below data we have ticket number(just filtered for 1 ticket), Modified group name(Group who modifies the ticket),
Modified Date(Date on which ticket has been modified by group.
Last column i am calculating difference of time on each line. i.e. when Modified date when Ticket first assigned to next group(B) - Modified Date when ticket first assigned to group A
I was trying to sum up last column group by Ticket number and Modified group name by formula
Sum(Aggr((interval((Below(Total ([Modified Date]),1,1)-[Modified Date]),'hh.m')),[Modified Group Name],[Ticket Number]))
But getting only zero for each line.
Please help!
Ticket Number | Modified Date | Modified Group Name | interval((Below(Total ([Modified Date]),1,1)-[Modified Date]),'hh.m') |
1781 | 10/3/2019 12:11 | A | 0.46 |
1781 | 10/3/2019 12:58 | B | 3.11 |
1781 | 10/3/2019 16:09 | C | 14.16 |
1781 | 10/4/2019 6:25 | A | 126.52 |
1781 | 10/10/2019 5:40 | B | 16.22 |
1781 | 10/11/2019 7:37 | C | 25.57 |
I am not sure I follow. Again, it would help me if you can provide the output you are hoping to get from the data provided.
Data :
Ticket Number | Team Name | Modified Date | Formula used for 4th column | |
1781 | A | 10/3/19 12:11 PM | 0:47 | C3-C2 |
1781 | B | 10/3/19 12:58 PM | 3:11 | C4-C3 |
1781 | B | 10/3/19 4:09 PM | 14:16 | C5-C4 |
1781 | S | 10/4/19 6:25 AM | 6:53 | C6-C5 |
1781 | G | 10/9/19 1:18 PM | 16:22 | C7-C6 |
1781 | S | 10/10/19 5:40 AM | 1:57 | C8-C7 |
1781 | S | 10/11/19 7:37 AM | 7:33 | C9-C8 |
1781 | G | 10/25/19 3:10 PM | 17:49 | C10-C9 |
1781 | B | 10/28/19 8:59 AM | 3:18 | C11-C10 |
1781 | S | 10/28/19 12:17 PM | 20:51 | C12-C11 |
1781 | D | 10/29/19 9:08 AM | 0:39 | C13-C12 |
1781 | G | 10/29/19 9:47 AM | 22:27 | C14-C13 |
1781 | S | 10/30/19 8:14 AM | 5:08 | C15-C14 |
1781 | S | 10/31/19 1:22 PM | 22:00 | C16-C15 |
1781 | G | 11/12/19 11:22 AM | 0:02 | C17-C16 |
1781 | G | 11/12/19 11:24 AM | 3:48 | C18-C17 |
1781 | B | 11/12/19 3:12 PM | 16:23 | C19-C18 |
1781 | S | 11/13/19 7:35 AM | 5:30 | C20-C19 |
1781 | S | 11/14/19 1:05 PM | 1:58 | C21-C20 |
1781 | G | 11/14/19 3:03 PM | 19:20 | C22-C21 |
1781 | S | 11/15/19 10:23 AM | 1:41 | C23-C22 |
1781 | S | 11/15/19 12:04 PM | 3:49 | C24-C23 |
1781 | G | 11/26/19 3:53 PM | 15:25 | C25-C24 |
1781 | S | 11/27/19 7:18 AM | 6:19 | C26-C25 |
1781 | S | 11/27/19 1:37 PM | 10:38 | C27-C26 |
1781 | A | 12/3/19 12:15 AM | 0:00 | C28-C27 |
Required calculation after above step:
Ticket Number | Team Name | Modified Date | Value calculated in above table |
1781 | A | 10/3/19 12:11 PM | 0:47 |
1781 | A | 12/3/19 12:15 AM | 0:00 |
Total | 0:47 |
Ticket Number | Team Name | Modified Date | |
1781 | B | 10/3/19 12:58 PM | 3:11 |
1781 | B | 10/3/19 4:09 PM | 14:16 |
1781 | B | 10/28/19 8:59 AM | 3:18 |
1781 | B | 11/12/19 3:12 PM | 16:23 |
Total | 13:08 |
Ticket Number | Team Name | Modified Date | |
1781 | D | 10/29/19 9:08 AM | 0:39 |
Total | 0:39 |
Ticket Number | Team Name | Modified Date | |
1781 | G | 10/9/19 1:18 PM | 16:22 |
1781 | G | 10/25/19 3:10 PM | 17:49 |
1781 | G | 10/29/19 9:47 AM | 22:27 |
1781 | G | 11/12/19 11:22 AM | 0:02 |
1781 | G | 11/12/19 11:24 AM | 3:48 |
1781 | G | 11/14/19 3:03 PM | 19:20 |
1781 | G | 11/26/19 3:53 PM | 15:25 |
Total | 7:48 |
Ticket Number | Team Name | Modified Date | |
1781 | S | 10/4/19 6:25 AM | 6:53 |
1781 | S | 10/10/19 5:40 AM | 1:57 |
1781 | S | 10/11/19 7:37 AM | 7:33 |
1781 | S | 10/28/19 12:17 PM | 20:51 |
1781 | S | 10/30/19 8:14 AM | 5:08 |
1781 | S | 10/31/19 1:22 PM | 22:00 |
1781 | S | 11/13/19 7:35 AM | 5:30 |
1781 | S | 11/14/19 1:05 PM | 1:58 |
1781 | S | 11/15/19 10:23 AM | 1:41 |
1781 | S | 11/15/19 12:04 PM | 3:49 |
1781 | S | 11/27/19 7:18 AM | 6:19 |
1781 | S | 11/27/19 1:37 PM | 10:38 |
Total | 22:17 |
So final result should be:
Ticket number | Team | Total of each team for this ticket |
1781 | A | 0:47 |
1781 | B | 1:55 |
1781 | D | 9:21 |
1781 | G | 7:48 |
1781 | S | 22.17 |
This is what I am not sure about... I can get the numbers you posted for the individual rows... but how are you calculating the total? What is the calculation here for the total?
This was my doubt actually, I am not able to calculate the total.. what i was trying is.. as we do in sql:
select 'Ticket Number,'Team Name',sum(4th column as calculated above)
from table group by 'Team number', 'Team Name';
I want to implement this in qliksense.
But how can Sum of four numbers (positive numbers) where one of the number is 16 be 13? Shouldn't it be at least 16? I mean it is basic math.
Yes you are right but as the calculated field format is hours and minutes so may be it got converted in excel
. but if you can calculate the total we can check it
For B, I am getting 37 hours and 8 minutes.
16 hours and 23 minutes
+ 14 hours and 16 minutes
30 hours and 39 minutes
+ 03 hours and 11 minutes
33 hours and 50 minutes
+ 03 hours and 18 minutes
37 hours and 08 minutes
and I guess your next question would be, what is the expression that I used and yet again I will tell you that it is the same expression as pointed above
Interval(
Sum(Aggr(
Interval(Below(TOTAL [Modified Date]) - [Modified Date], 'hh.m')
, [Ticket Number], ([Modified Date], (NUMERIC))))
, 'hh:mm')
Taking a step back, do you really know what you want here?
Hello sunny, Finally its good for me now:
Interval(
sum(Aggr
(Interval
(Below(TOTAL [Modified Date]) - [Modified Date], 'hh.mm') , [Ticket Number],([Modified Date], (NUMERIC)))), 'hh:mm')
Now I am having another doubt, as I have multiple tickets , So when we are doing below row modified date minus current modified date row..it works correct but when one ticket lines gets over and another tickets comes, there will be negative value as small date minus big date is happening. I want to use Rangemax to this expression to make "0" where negative values are coming . please help me to replace negative value by 0.
Ticket Number | Team Name | Modified Date | Interval(sum(Aggr(Interval(Below(TOTAL [Modified Date]) - [Modified Date], 'hh.mm') , [Ticket Number],([Modified Date], (NUMERIC)))), 'hh:mm') |
Total | 909.52 | ||
17672486 | A | 9/13/2019 6:01 | 297:25 |
17672486 | C | 9/25/2019 15:26 | 112:49 |
17672486 | A | 9/30/2019 8:16 | 33:14 |
17672486 | C | 10/1/2019 17:30 | 45:55 |
17672486 | C | 10/3/2019 15:26 | 1:44 |
17672486 | D | 10/3/2019 17:11 | 0:30 |
17672486 | C | 10/3/2019 17:41 | 139:06 |
17672486 | B | 10/9/2019 12:48 | 0:09 |
17672486 | C | 10/9/2019 12:57 | 162:49 |
17672486 | G | 10/16/2019 7:47 | 0:10 |
17672486 | C | 10/16/2019 7:58 | 30:31 |
17672486 | G | 10/17/2019 14:30 | 0:42 |
17672486 | C | 10/17/2019 15:12 | 19:51 |
17672486 | O | 10/18/2019 11:04 | 1:54 |
17672486 | C | 10/18/2019 12:58 | 0:30 |
17672486 | A | 10/18/2019 13:29 | 65:49 |
17672486 | C | 10/21/2019 7:18 | 7:57 |
17672486 | O | 10/21/2019 15:15 | 21:29 |
17672486 | C | 10/22/2019 12:45 | 19:34 |
17672486 | O | 10/23/2019 8:19 | 0:03 |
17672486 | C | 10/23/2019 8:23 | 50:28 |
17672486 | C | 10/25/2019 10:52 | 1:32 |
17672486 | O | 10/25/2019 12:24 | 2:20 |
17672486 | C | 10/25/2019 14:44 | -727:56 |
17674710 | A | 9/13/2019 10:04 | 0:31 |
17674710 | C | 9/13/2019 10:36 | 74:21 |
17674710 | A | 9/16/2019 12:57 | 0:50 |
17674710 | C | 9/16/2019 13:48 | 0:33 |
17674710 | A | 9/16/2019 14:21 | 167:19 |
17674710 | C | 9/23/2019 13:40 | 64:39 |
17674710 | B | 9/26/2019 6:20 | 4:21 |
17674710 | C | 9/26/2019 10:42 | -316:40 |
17759183 | A | 9/25/2019 6:48 | 2:08 |
17759183 | C | 9/25/2019 8:57 | 391:02 |
17759183 | A | 10/11/2019 15:59 | 64:36 |
17759183 | C | 10/14/2019 8:36 | 20:25 |
17759183 | B | 10/15/2019 5:01 | 1:46 |
17759183 | C | 10/15/2019 6:47 | 80:54 |
17759183 | A | 10/18/2019 15:42 | 64:14 |
17759183 | C | 10/21/2019 7:56 | 0:00 |
May be this
Interval(Sum(Aggr(Interval(RangeMax(Below(TOTAL [Modified Date]) - [Modified Date], 0), 'hh.mm') , [Ticket Number], ([Modified Date], (NUMERIC)))), 'hh:mm')
or may be this
Interval(
Sum(Aggr(
Interval(Below([Modified Date]) - [Modified Date], 'hh.m')
, [Ticket Number], ([Modified Date], (NUMERIC))))
, 'hh.mm')