topic Re: AGGR (Nested?) Aim: Count only once across 4 fields in QlikView

AGGR (Nested?) Aim: Count only once across 4 fields

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

Dear all,

I need to aggregate the following. I tried nested aggr but think I may not have gotten the brackets and commas right.

For 1 layer of aggr, I sourced the following:

Aggr(NODISTINCT If([Type (Rank)] = Min(TOTAL <[Group Name]> [Type (Rank)]), [Group Name]), [Group Name], [Type (Rank)])

What I need is for the same group name, I only want to show the Group Name with

(1) the smallest Type (Rank)

(2) the earliest date

(3) largest amount for Process

e.g.

Group Name	Type (Rank)	Date	Process	Amount	Outcome (Aggr Group Name)
A	1	1 Feb 2020	Pabc	10	A
A	2	1 Jan 2020	Pdef	20
A	2	1 Jan 2020	Pabc	30
B	3	1 Apr 2020	Pabc	10
B	3	1 Mar 2020	Pabc	20	B
C	1	1 Mar 2020	Pdef	10
C	1	1 Mar 2020	Pabc	8	C
C	1	1 Mar 2020	Pabc	8	C
C	1	1 Apr 20	Pdef	10
C	2	1 Feb 20	Pabc	20

I will do a Distinct on the outcome to obtain the final count.

Many thanks in advance!

Re: AGGR (Nested?) Aim: Count only once across 4 fields

sunny_talwar — Fri, 25 Sep 2020 13:37:14 GMT

May be this

Aggr(NODISTINCT If(([Type (Rank)]*1e10)+(Date*1e4)-Sum(Amount) = Min(TOTAL <[Group Name]> Aggr(([Type (Rank)]*1e10)+(Date*1e4)-Sum(Amount), [Group Name], [Type (Rank)], Date, Process)), [Group Name]), [Group Name], [Type (Rank)], Date, Process)

Re: AGGR (Nested?) Aim: Count only once across 4 fields

NewbieToQV — Wed, 30 Sep 2020 15:46:32 GMT

Wow, that works for the case that I wrote. I tested on the dataset that I have and noticed that I missed out on a scenario.

Thus, the criteria is

(1) the smallest Type (Rank)

(2) the earliest date

(3) largest amount for Process

(4) If amount is the same then ID_Num by ascending order

p Name	ID_Num	Type (Rank)	Date	Process	Amount	Outcome (Aggr Group Name)
A	ID1	1	1 Feb 2020	Pabc	10	A
A	ID2	2	1 Jan 2020	Pdef	20
A	ID3	2	1 Jan 2020	Pabc	30
B	ID4	3	1 Apr 2020	Pabc	10
B	ID5	3	1 Mar 2020	Pabc	20	B
C	ID6	1	1 Mar 2020	Pdef	10
C	ID7	1	1 Mar 2020	Pabc	8	C
C	ID8	1	1 Mar 2020	Pabc	8	C
C	ID9	1	1 Apr 20	Pdef	10
C	ID10	2	1 Feb 20	Pabc	20
D	ID11	1	1 Mar 2020	Pdef	16	D
D	ID12	1	1 Mar 2020	Pabc	8
D	ID13	1	1 Mar 2020	Pabc	8
D	ID14	1	1 Apr 20	Pdef	10
D	ID15	2	1 Feb 20	Pabc	20

Grateful for your help again.

Re: AGGR (Nested?) Aim: Count only once across 4 fields

NewbieToQV — Wed, 30 Sep 2020 15:45:40 GMT

Tried to play around with the formulas but still stuck.... tried to add a rank formula
-aggr(rank(-sum(Amount),0,1),ID_Num) to try to derive the minimum but did not work. What am I missing on?

Re: AGGR (Nested?) Aim: Count only once across 4 fields

sunny_talwar — Thu, 01 Oct 2020 11:56:38 GMT

@NewbieToQV If we need to decide first by Amount, why is ID7 and ID8 C here? Shouldn't it be ID6?

So, I changed ID6 from 10 to 4... but then if we have to choose by Rank of ID_Num, we will have C next to only one ID_Num and I am guessing it would be ID7?

Try this

Aggr(NODISTINCT If(([Type (Rank)]*1e10)+(Date*1e4)-Sum(Amount)-(Rank(ID_Num)/1E4) = Min(TOTAL <[Group Name]> Aggr(([Type (Rank)]*1e10)+(Date*1e4)-Sum(Amount)-(Rank(ID_Num)/1E4), [Group Name], [Type (Rank)], Date, Process, ID_Num)), [Group Name]) , [Group Name], [Type (Rank)], Date, Process, ID_Num)

Re: AGGR (Nested?) Aim: Count only once across 4 fields

NewbieToQV — Fri, 02 Oct 2020 02:31:44 GMT

Hi @sunny_talwar , thanks for responding.

On the question of Group C that why ID7 and ID8 is tagged is because I need to count (and mark) to 1 of the IDs and ID7 takes precedence because it is part of the largest category of amounts in process (after rank and date).

"Pabc (ID7 and ID8 )= 16" > "Pdef (ID6) = 10"

I have attached the qvw for testing as well.

Re: AGGR (Nested?) Aim: Count only once across 4 fields

Brett_Bleess — Tue, 20 Oct 2020 15:52:20 GMT

About the only things I have are some Design Blog posts that may be of some help:

https://community.qlik.com/t5/Qlik-Design-Blog/The-nodistinct-qualifier-and-the-Aggr-function/ba-p/1535332

https://community.qlik.com/t5/Qlik-Design-Blog/Pitfalls-of-the-Aggr-function/ba-p/1463275

Hopefully they may be of some use.

Regards,
Brett