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

[NewbieToQV]

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!

[sunny_talwar]

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)

[NewbieToQV]

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.

[NewbieToQV]

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?

[sunny_talwar]

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

[NewbieToQV]

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.

[Brett_Bleess]

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/1...

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