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

[sunny_talwar]

How about this

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

, [Ticket Number], ([Modified Date], (NUMERIC))))

[rathorep]

Hi.. I tried this but not working

[sunny_talwar]

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?

[rathorep]

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

[sunny_talwar]

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

[rathorep]

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

[sunny_talwar]

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

[rathorep]

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.

[sunny_talwar]

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?