Group by in front end

[rathorep]

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

[rathorep]

Answer to your first question

1. I just took few line of my data so in my full data last value is 0. you can consider 7.32 as 0 here .

2. i think because of data format or due to sorting in excel the correct value got disturbed. but you have understood the correct logic.

[sunny_talwar]

I do get the logic and I think this works for me... now it doesn't match what you have, but that is because you have your output based on different input...

Interval(
  Sum(Aggr(
    
    Interval(Below(TOTAL [Modified Date]) - [Modified Date], 'hh.m')

  , [Ticket Number], ([Modified Date], (NUMERIC))))
, 'hh.mm')

If it doesn't, then please provide a sample where it doesn't match the output based on the input you have provided... otherwise we will keep on comparing "Apples" with "Oranges" and will never get a solution for this 🙂

Best,
Sunny

[anushree1]

@sunny_talwar ,

Could you please help me understand the need of total keyword in the below function , i tried without total it gives null,

I am of the understanding that total is used to disregard the dimensions in the chart, and here it does'nt seem required , it would be helpful if you could clear this for me

also ,the below expression is giving out Null, not sure why ,i did try encapsulating it across formatting functions but still no change

Interval(Below(TOTAL [Modified Date]) - [Modified Date], 'hh.m')

i have used web connection and loaded the data onto Qlik Sense

[rathorep]

Please take this data and  help me to calculate amount of time spend by each team on this ticket.. Please keep in mind that I have many tickets.

Ignore other calculated column i shared above as that I was trying to find this result.

Ticket Number	Team Name	Modified Date
1781	A	10/3/2019 12:11
1781	B	10/3/2019 12:58
1781	B	10/3/2019 16:09
1781	S	10/4/2019 6:25
1781	G	10/9/2019 13:18
1781	S	10/10/2019 5:40
1781	S	10/11/2019 7:37
1781	G	10/25/2019 15:10
1781	B	10/28/2019 8:59
1781	S	10/28/2019 12:17
1781	D	10/29/2019 9:08
1781	G	10/29/2019 9:47
1781	S	10/30/2019 8:14
1781	S	10/31/2019 13:22
1781	G	11/12/2019 11:22
1781	G	11/12/2019 11:24
1781	B	11/12/2019 15:12
1781	S	11/13/2019 7:35
1781	S	11/14/2019 13:05
1781	G	11/14/2019 15:03
1781	S	11/15/2019 10:23
1781	S	11/15/2019 12:04
1781	G	11/26/2019 15:53
1781	S	11/27/2019 7:18
1781	S	11/27/2019 13:37
1781	A	12/3/2019 0:15

[sunny_talwar]

Is this the output you expect to see from the sample provided?

[rathorep]

How you have calculated 00.39 for D and for other team? 

[sunny_talwar]

Based on the expression I provided? Is this not correct? If not then what are the correct numbers?

[rathorep]

No i actually can not see the expression you have used to calculate this value ...

[sunny_talwar]

This would be the third time I am sharing the expression since we have started looking at this

Interval(
  Sum(Aggr(
    
    Interval(Below(TOTAL [Modified Date]) - [Modified Date], 'hh.m')

  , [Ticket Number], ([Modified Date], (NUMERIC))))
, 'hh.mm')

[rathorep]

Expression is correct but if we take below 2 columns

Ticket number and modified date:

then it means we are grouping data on these 2 columns , but I think we have to group the data on ticket number and team name .  so that we can sum up based on each team?