Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
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.
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?
@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)
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.
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/Pitfalls-of-the-Aggr-function/ba-p/1463275
Hopefully they may be of some use.
Regards,
Brett