Aggregations and group by

andrea0901 — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,
 
below is my scenario. i have done in the excel according to the requirement, but in qlik i am not able to do. 
The calculations i need to do is from I column to O Column based on aggregations. please help me it wil be very helpful.
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
IN#
Workflow.companyCode
Workflow.fiDocumentNumber
Workflow.mmDocumentNumber
Workflow.invoiceLineItemNumber
Workflow.Status
Events.ASSIGNED_TIMESTAMP
Events.CLOSED_TIMESTAMP
Action_v2
Workflow_Action_v2
Action duration
Workflow+item
Workflow item with workflow block
Workflow duration
Duration
10050043
BE12
5100000012
5105720537
1
Terminated
9/6/2018 17:43
9/8/2018 17:43
Assign approver
D2&"_"&E2&"_"&R2
SUMIF(S:S,S2,X:X)
D2&"_"&E2
U2&"_"&G2
SUMIF(U:U,U2,X:X)
Q2-O2
10050043
BE12
5100000012
5105720537
1
Terminated
9/8/2018 17:43
9/10/2018 17:43
Assign approver
5105720537_1_Assign approver
58
5105720537_1
5105720537_1_priceBlock
108
2
10050043
BE12
5100000012
5105720537
1
Terminated
9/10/2018 17:43
9/12/2018 17:43
Assign approver
5105720537_1_Assign approver
58
5105720537_1
5105720537_1_priceBlock
108
2
10050043
BE12
5100000012
5105720537
1
Terminated
9/12/2018 17:43
9/14/2018 17:43
Assign approver
5105720537_1_Assign approver
58
5105720537_1
5105720537_1_priceBlock
108
2
10050043
BE12
5100000012
5105720537
1
Terminated
9/14/2018 17:43
9/16/2018 17:43
Assign approver
5105720537_1_Assign approver
58
5105720537_1
5105720537_1_priceBlock
108
2
10050043
BE12
5100000012
5105720537
1
Terminated
9/16/2018 17:43
9/18/2018 17:43
Assign approver
5105720537_1_Assign approver
58
5105720537_1
5105720537_1_priceBlock
108
2
10050043
BE12
5100000012
5105720537
1
Terminated
9/18/2018 17:43
9/20/2018 17:43
Assign approver
5105720537_1_Assign approver
58
5105720537_1
5105720537_1_priceBlock
108
2
10050043
BE12
5100000012
5105720537
1
Terminated
9/20/2018 17:43
9/22/2018 17:43
Assign approver
5105720537_1_Assign approver
58
5105720537_1
5105720537_1_priceBlock
108
2
10050043
BE12
5100000012
5105720537
1
Terminated
9/22/2018 17:43
9/24/2018 17:43
Assign approver
5105720537_1_Assign approver
58
5105720537_1
5105720537_1_priceBlock
108
2
10050043
BE12
5100000012
5105720537
1
Terminated
9/24/2018 17:43
9/26/2018 17:43
Assign approver
5105720537_1_Assign approver
58
5105720537_1
5105720537_1_priceBlock
108
2
10050043
BE12
5100000012
5105720537
1
Terminated
9/26/2018 17:43
9/28/2018 17:43
Assign approver
5105720537_1_Assign approver
58
5105720537_1
5105720537_1_priceBlock
108
2
10050043
BE12
5100000012
5105720537
1
Terminated
9/28/2018 17:43
9/30/2018 17:43
Assign approver
5105720537_1_Assign approver
58
5105720537_1
5105720537_1_priceBlock
108
2
10050043
BE12
5100000012
5105720537
1
Terminated
9/30/2018 17:43
10/2/2018 17:43
Assign approver
5105720537_1_Assign approver
58
5105720537_1
5105720537_1_priceBlock
108
2
10050043
BE12
5100000012
5105720537
1
Terminated
10/2/2018 17:43
10/4/2018 17:43
Assign approver
5105720537_1_Assign approver
58
5105720537_1
5105720537_1_priceBlock
108
2
10050043
BE12
5100000012
5105720537
1
Terminated
10/4/2018 17:43
10/6/2018 17:43
Assign approver
5105720537_1_Assign approver
58
5105720537_1
5105720537_1_priceBlock
108
2
10068131
BE12
5100000019
5105720624
1
Terminated
9/29/2018 10:33
10/1/2018 10:33
Assign approver
5105720624_1_Assign approver
54
5105720624_1
5105720624_1_priceBlock
63
2
10068131
BE12
5100000019
5105720624
1
Terminated
10/1/2018 10:33
10/3/2018 10:33
Assign approver
5105720624_1_Assign approver
54
5105720624_1
5105720624_1_priceBlock
63
2
10068131
BE12
5100000019
5105720624
1
Terminated
10/3/2018 10:33
10/5/2018 10:33
Assign approver
5105720624_1_Assign approver
54
5105720624_1
5105720624_1_priceBlock
63
2
10068152
BE12
5100000020
5105720625
1
Terminated
9/21/2018 10:34
9/23/2018 10:34
Assign approver
5105720625_1_Assign approver
54
5105720625_1
5105720625_1_priceBlock
63
2
10068152
BE12
5100000020
5105720625
1
Terminated
9/23/2018 10:34
9/25/2018 10:34
Assign approver
5105720625_1_Assign approver
54
5105720625_1
5105720625_1_priceBlock
63
2
10068152
BE12
5100000020
5105720625
1
Terminated
9/25/2018 10:34
9/27/2018 10:34
Assign approver
5105720625_1_Assign approver
54
5105720625_1
5105720625_1_priceBlock
63
2
11327948
BE12
5100000333
5105762436
1
Terminated
11/10/2018 14:43
##########
Aprprover
5105762436_1_Aprprover
27
5105762436_1
5105762436_1_itemAmountBlock
35
5
12769199
BE12
5100001578
5105802564
116
Active
12/13/2018 10:03
##########
Assign approver
5105802564_116_Assign approver
12
5105802564_116
5105802564_116_itemAmountBlock
12
2
12769199
BE12
5100001578
5105802564
116
Active
12/15/2018 10:03
##########
Assign approver
5105802564_116_Assign approver
12
5105802564_116
5105802564_116_itemAmountBlock
12
2
12769199
BE12
5100001578
5105802564
116
Active
12/17/2018 10:03
##########
Assign approver
5105802564_116_Assign approver
12
5105802564_116
5105802564_116_itemAmountBlock
12
2
12769209
BE12
5100001578
5105802564
117
Active
12/7/2018 10:02
12/9/2018 10:02
Assign approver
5105802564_117_Assign approver
12
5105802564_117
5105802564_117_itemAmountBlock
12
2
12769209
BE12
5100001578
5105802564
117
Active
12/9/2018 10:02
##########
Assign approver
5105802564_117_Assign approver
12
5105802564_117
5105802564_117_itemAmountBlock
12
2
12769209
BE12
5100001578
5105802564
117
Active
12/11/2018 10:02
##########
Assign approver
5105802564_117_Assign approver
12
5105802564_117
5105802564_117_itemAmountBlock
12
2
12769209
BE12
5100001578
5105802564
117
Active
12/13/2018 10:03
##########
Assign approver
5105802564_117_Assign approver
12
5105802564_117
5105802564_117_itemAmountBlock
12
2
12769209
BE12
5100001578
5105802564
117
Active
12/15/2018 10:03
##########
Assign approver
5105802564_117_Assign approver
12
5105802564_117
5105802564_117_itemAmountBlock
12
2
12769209
BE12
5100001578
5105802564
117
Active
12/17/2018 10:03
##########
Assign approver
5105802564_117_Assign approver
12
5105802564_117
5105802564_117_itemAmountBlock
12
2
12769492
BE12
5100001869
5105802581
1
Terminated
12/10/2018 17:35
12/11/2018 8:27
Aprprover
5105802581_1_Aprprover
1
5105802581_1
5105802581_1_itemAmountBlock
4
1

Re: Aggregations and group by

sunny_talwar — Tue, 05 Feb 2019 12:50:19 GMT

Would you be able to share your Excel file?

Re: Aggregations and group by

andrea0901 — Tue, 05 Feb 2019 12:58:40 GMT

i need to calculate the average of work duration for each invoice

Re: Aggregations and group by

sunny_talwar — Tue, 05 Feb 2019 12:59:41 GMT

okay, can you provide the Excel file?

Re: Aggregations and group by

andrea0901 — Tue, 05 Feb 2019 13:01:37 GMT

sorry sunny i cant share the file.

Re: Aggregations and group by

sunny_talwar — Tue, 05 Feb 2019 13:08:07 GMT

All I am asking for is the Excel file from which you shared the above data from? If you are able to share the data, why is sharing the Excel a problem? I am just trying to see what is R2 here?

You only shared the data up until column O, what is R here?

Re: Aggregations and group by

sunny_talwar — Tue, 05 Feb 2019 13:10:33 GMT

You also have reference to Column U, I am not sure how to read the formulas you have given.

Re: Aggregations and group by

andrea0901 — Tue, 05 Feb 2019 13:12:46 GMT

its not R column, it s I column where the column name is Action_v2

sorry for the typo error.

Re: Aggregations and group by

andrea0901 — Tue, 05 Feb 2019 13:18:25 GMT

Hi Sunny,

I will share u the excel file.

topic Re: Aggregations and group by in QlikView

Aggregations and group by

Re: Aggregations and group by

Re: Aggregations and group by

Re: Aggregations and group by

Re: Aggregations and group by

Re: Aggregations and group by

Re: Aggregations and group by

Re: Aggregations and group by

Re: Aggregations and group by