topic Re: Group by in front end in Qlik Compose

Group by in front end

rathorep — Fri, 14 Mar 2025 20:49:07 GMT

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

Re: Group by in front end

sunny_talwar — Mon, 16 Mar 2020 12:21:22 GMT

How about this

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

Re: Group by in front end

rathorep — Tue, 17 Mar 2020 12:27:37 GMT

Hi.. I tried this but not working

Re: Group by in front end

sunny_talwar — Tue, 17 Mar 2020 12:39:22 GMT

Can you elaborate a little on what exactly do you mean that it is not working? Does it give you wrong numbers or do you get null?

Re: Group by in front end

rathorep — Wed, 18 Mar 2020 11:35:00 GMT

This is the result set I am getting

Ticket Number	Modified Group Name	Field Modified Date	interval((Below(Total ([Field Modified Date]),1,1)-[Field Modified Date]),'hh.m')	Sum(Aggr(Interval(Below(TOTAL [Field Modified Date]) - [Field Modified Date], 'hh.m'), [Ticket Number],[Field Modified Date], ([Field Modified Date], (NUMERIC))))
Total				-60.33724537
1781	A	10/3/2019 12:11	00.46	0.032557870370511
1781	B	10/3/2019 12:58	03.11	0.13273148147709
1781	C	10/3/2019 16:09	14.16	0
1781	A	10/4/2019 6:25	06.52	-0.75986111110979
1781	B	10/9/2019 13:18	16.22	-5.2864699074053
1781	C	10/10/2019 5:40	01.57	-0.6825115740794
1781	B	10/11/2019 7:37	07.32	-1.0812847222187

Re: Group by in front end

sunny_talwar — Wed, 18 Mar 2020 11:37:45 GMT

This is displaying the output as a number, I guess you need it as duration... try this

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

Re: Group by in front end

rathorep — Wed, 18 Mar 2020 11:38:36 GMT

Result set which i want is:

Ticket Number	Modified Group Name	Total hours spent by each team
1781	A	127.39
1781	B	363.06
1781	C	40:13:00

Re: Group by in front end

sunny_talwar — Wed, 18 Mar 2020 12:33:10 GMT

How are you getting those numbers? Can you elaborate please?

Re: Group by in front end

rathorep — Thu, 19 Mar 2020 07:38:33 GMT

Ticket Number	Modified Group Name	Field Modified Date	interval((Below(Total ([Field Modified Date]),1,1)-[Field Modified Date]),'hh.m')	Sum(Aggr(Interval(Below(TOTAL [Field Modified Date]) - [Field Modified Date], 'hh.m'), [Ticket Number],[Field Modified Date], ([Field Modified Date], (NUMERIC))))
Total				-60.33724537
1781	A	10/3/2019 12:11	0.46	0.03255787
1781	B	10/3/2019 12:58	3.11	0.132731481
1781	C	10/3/2019 16:09	14.16	0
1781	A	10/4/2019 6:25	6.52	-0.759861111
1781	B	10/9/2019 13:18	16.22	-5.286469907
1781	C	10/10/2019 5:40	1.57	-0.682511574
1781	B	10/11/2019 7:37	7.32	-1.081284722


Ticket Number	Modified Group Name	Total hours spent by each team
1781	A	6.98	All values under 4th column belongs to A team i.e. 0.46+6.52
1781	B	26.65	All values under 4th column belongs to B team i.e. 3.11+16.22+7.32
1781	C	15.73	All values under 4th column belongs to C team i.e. 14.16+1.57

Basically 4th column is having value (10/3/2019 12:58:00 PM - 10/3/2019 12:11:00 PM= 0.46) which is the time calculated when ticket is assigned to B minus ticket assigned to A.

Re: Group by in front end

sunny_talwar — Thu, 19 Mar 2020 12:02:06 GMT

This make sense

"Basically 4th column is having value (10/3/2019 12:58:00 PM - 10/3/2019 12:11:00 PM= 0.46) which is the time calculated when ticket is assigned to B minus ticket assigned to A."

But can you go over these

1) How did you arrive at 7.32... 10/11/2019 7:37 is the last row... what x - 10/11/2019 7:37 = 7.32... what is x here?

2) How did you get 6.52? 10/9/2019 13:18 - 10/4/2019 6:25 would be several days so should be more than 24 hours?

Re: Group by in front end

rathorep — Thu, 19 Mar 2020 17:44:02 GMT

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.

Re: Group by in front end

sunny_talwar — Thu, 19 Mar 2020 18:58:26 GMT

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

Re: Group by in front end

anushree1 — Thu, 19 Mar 2020 19:26:07 GMT

@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

Re: Group by in front end

rathorep — Wed, 25 Mar 2020 19:09:55 GMT

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

Re: Group by in front end

sunny_talwar — Thu, 26 Mar 2020 12:01:26 GMT

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

Re: Group by in front end

rathorep — Tue, 31 Mar 2020 13:29:50 GMT

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

Re: Group by in front end

sunny_talwar — Tue, 31 Mar 2020 14:53:42 GMT

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

Re: Group by in front end

rathorep — Tue, 31 Mar 2020 16:05:34 GMT

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

Re: Group by in front end

sunny_talwar — Tue, 31 Mar 2020 16:10:52 GMT

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')

Re: Group by in front end

rathorep — Wed, 01 Apr 2020 17:07:11 GMT

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?