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]

Perfectly worked ..Thanks alotttttt. you are genious 🙂 

[rathorep]

just one more doubt,

When i am calculating avg with the same formula(just replaced Sum with AVG:

Interval(
AVG(Aggr(

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

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

then At each line when ticket number gets changed and negative difference value is coming, I am getting Hyphn(-) instead of 0. while calculating sum its correct. only while calculating AVG its coming hyphn.

I did not understand if avg should be calculated like this?

[sunny_talwar]

May be try this

Interval(
Alt(
AVG(Aggr(

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

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

[rathorep]

Last formula is not working well, but when i used

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

which you have shared is working fine except one thing 

For below formula of Sum its giving result -Total sum: 656.06.57 for some set of records but actual result should be 17.06.57

which means in d.hh.mm "d" is not getting correctly sum up.

Interval(Sum(Aggr(Interval(RangeMax(Below(TOTAL [Field Modified Date]) - [Field Modified Date], 0), 'd.hh.mm') ,
[Win@proach Ticket Number], ([Field Modified Date], (NUMERIC)))), 'd.hh.mm')

Avg by below calculation is correct :

Interval(AVG(Aggr(Interval(RangeMax(Below(TOTAL [Field Modified Date]) - [Field Modified Date], 0), 'd.hh.mm') , [Win@proach Ticket Number],([Field Modified Date], (NUMERIC)))), 'd.hh:mm')

Please help me to calculate sum with correct days, As hours and minutes are coming correctly

[rathorep]

From the last script you mentioned, i am getting zero in calculating difference in each line.

 Avg is getting calculated correct by below formula as you have shared before-

 Interval(AVG(Aggr(Interval(RangeMax(Below(TOTAL [Field Modified Date]) - [Field Modified Date], 0), 'd.hh.mm') , [Win@proach Ticket Number],([Field Modified Date], (NUMERIC)))), 'd.hh:mm')

But for calculating sum, I am getting Total of all rows: 656.06:57 in d.hh.mm format for some data set ,which should be 17.06:57. it means "d" is not getting sum up correctly, hh.mm are fine.

Interval(sum(Aggr(Interval(RangeMax(Below(TOTAL [Field Modified Date]) - [Field Modified Date], 0), 'd.hh.mm') , [Win@proach Ticket Number],([Field Modified Date], (NUMERIC)))), 'd.hh:mm')

Please help .

[sunny_talwar]

Can you share a sample where we can see the issue?

[rathorep]

 

Screenshot -Sum and AVG I am getting in Qlik in separate column.

Data attached- Calculating sum and avg in excel.

[sunny_talwar]

In the screenshot... what is not correct?

[rathorep]

Total effort calculated in screenshot and in the excel file attached is coming different. Total Effort calculated in excel seems correct as in screenshot it is : 39:15:28 means 39 days 15 min and 28 sec . Data i have shared only belongs to march 2020 so its not possible as its showing 39 days

[sunny_talwar]

I don't think 39 is the days, I think it is hours.... meaning 1 day and 15 hours