Solved: Aggregate Rank() on a field - Page 2 - Qlik Community

qlikwiz123 · ‎2019-05-16

Please look at the screenshot below.

I just sorted on Funds from Highest to lowest and since CVS HEALTH has the highest Funds overall, the Rank for all CVS from 'Giver' column should be 1.

Then all rows with 'Giver' - ANHEUSER-BUSCH CO/INBEV should be 2, COMCAST A should be 3 and so on...

Nicole-Smith · ‎2019-05-17

Actually, the sum I have is working properly. There are some Funds you have more than one of so that needs to be taken into account in the sum:

This is data from a table made directly from your example file. The sum of JPM is definitely higher than the sum of CVS when taking the quantity (count Funds) into account:

Cat	Company	Date	Giver	Funds	count(Funds)	sum(total <Cat> Funds)	=Aggr(Rank(aggr(Sum(Funds),Cat), 1, 1),Cat)
JPM	B	7/16/2018	JPMORGAN CHASE & CO	2250000000	2	40375000000	1
JPM	B	11/28/2018	JP MORGAN CHASE BANK NA	2500000000	2	40375000000	1
JPM	B	1/16/2018	JPMORGAN CHASE & CO	2250000000	1	40375000000	1
JPM	B	1/16/2018	JPMORGAN CHASE & CO	1750000000	1	40375000000	1
JPM	B	1/29/2018	JP MORGAN CHASE BANK NA	1250000000	1	40375000000	1
JPM	B	1/29/2018	JP MORGAN CHASE BANK NA	1000000000	1	40375000000	1
JPM	B	2/8/2018	JP MORGAN CHASE BANK NA	1750000000	1	40375000000	1
JPM	B	4/16/2018	JPMORGAN CHASE & CO	2250000000	1	40375000000	1
JPM	B	4/16/2018	JPMORGAN CHASE & CO	1750000000	1	40375000000	1
JPM	B	4/16/2018	JPMORGAN CHASE & CO	500000000	1	40375000000	1
JPM	B	4/19/2018	JP MORGAN CHASE BANK NA	2200000000	1	40375000000	1
JPM	B	4/19/2018	JP MORGAN CHASE BANK NA	1300000000	1	40375000000	1
JPM	B	6/11/2018	JPMORGAN CHASE & CO	1400000000	1	40375000000	1
JPM	B	6/11/2018	JPMORGAN CHASE & CO	1100000000	1	40375000000	1
JPM	B	7/16/2018	JPMORGAN CHASE & CO	1000000000	1	40375000000	1
JPM	B	7/18/2018	JPMORGAN CHASE & CO (TAP)	250000000	1	40375000000	1
JPM	B	8/23/2018	JPMORGAN CHASE BANK NA	1250000000	1	40375000000	1
JPM	B	9/17/2018	JPMORGAN CHASE & CO	1475000000	1	40375000000	1
JPM	B	10/12/2018	JP MORGAN CHASE BANK NA	800000000	1	40375000000	1
JPM	B	1/16/2019	JP MORGAN CHASE & CO	1850000000	1	40375000000	1
JPM	B	1/22/2019	JP MORGAN CHASE & CO	2000000000	1	40375000000	1
JPM	B	2/12/2019	JP MORGAN CHASE BANK	1500000000	1	40375000000	1
JPM	B	3/15/2019	J.P. MORGAN CHASE & CO.	2250000000	1	40375000000	1
CVS	A	3/6/2018	CVS HEALTH A	5000000000	2	40000000000	2
CVS	A	3/6/2018	CVS HEALTH A	1000000000	2	40000000000	2
CVS	A	3/6/2018	CVS HEALTH A	9000000000	1	40000000000	2
CVS	A	3/6/2018	CVS HEALTH A	8000000000	1	40000000000	2
CVS	A	3/6/2018	CVS HEALTH A	6000000000	1	40000000000	2
CVS	A	3/6/2018	CVS HEALTH A	3000000000	1	40000000000	2
CVS	A	3/6/2018	CVS HEALTH A	2000000000	1	40000000000	2

qlikwiz123 · ‎2019-05-17

I understand. But I don't have to sum the Funds column and then rank it. Instead, just look at the number on the Funds column, and then rank it accordingly with respect to 'Cat' field.

Is there anyway I can achieve this ranking purely based on the Number on the Funds Field? Exactly like I showed in the above screenshot. Since Cat - CVS has highest number among all, the rank for all CVS rows will be 1. Similarly, Cat - ABIBB has 2nd highest number in the Funds column in the entire data, so all ABIBB rows will be ranked 2. And so on...

Nicole-Smith · ‎2019-05-17

So it sounds like you want max() instead of sum()?

Using the following as a dimension:
=Aggr(Rank(aggr(max(Funds),Cat), 1, 1),Cat)

Results in this:

Cat	Company	Date	Giver	Funds	max(total <Cat> Funds)	=Aggr(Rank(aggr(max(Funds),Cat), 1, 1),Cat)
CVS	A	3/6/2018	CVS HEALTH A	9000000000	9000000000	1
CVS	A	3/6/2018	CVS HEALTH A	8000000000	9000000000	1
CVS	A	3/6/2018	CVS HEALTH A	6000000000	9000000000	1
CVS	A	3/6/2018	CVS HEALTH A	5000000000	9000000000	1
CVS	A	3/6/2018	CVS HEALTH A	3000000000	9000000000	1
CVS	A	3/6/2018	CVS HEALTH A	2000000000	9000000000	1
CVS	A	3/6/2018	CVS HEALTH A	1000000000	9000000000	1
ABIBB	A	3/20/2018	ANHEUSER-BUSCH INBEV WOR	2500000000	4250000000	2
ABIBB	A	3/20/2018	ANHEUSER-BUSCH INBEV WOR	1500000000	4250000000	2
ABIBB	A	3/20/2018	ANHEUSER-BUSCH INBEV WOR	500000000	4250000000	2
ABIBB	A	1/10/2019	ANHEUSER-BUSCH CO/INBEV	4250000000	4250000000	2
ABIBB	A	1/10/2019	ANHEUSER-BUSCH CO/INBEV	4000000000	4250000000	2
ABIBB	A	1/10/2019	ANHEUSER-BUSCH CO/INBEV	2500000000	4250000000	2
ABIBB	A	1/10/2019	ANHEUSER-BUSCH CO/INBEV	2000000000	4250000000	2
ABIBB	A	1/10/2019	ANHEUSER-BUSCH CO/INBEV	750000000	4250000000	2
CMCSA	A	2/1/2018	COMCAST A	1200000000	4000000000	3
CMCSA	A	2/1/2018	COMCAST A	1000000000	4000000000	3
CMCSA	A	10/2/2018	COMCAST A	4000000000	4000000000	3
CMCSA	A	10/2/2018	COMCAST A	3000000000	4000000000	3
CMCSA	A	10/2/2018	COMCAST A	2500000000	4000000000	3
CMCSA	A	10/2/2018	COMCAST A	2000000000	4000000000	3
CMCSA	A	10/2/2018	COMCAST A	1750000000	4000000000	3
CMCSA	A	10/2/2018	COMCAST A	1500000000	4000000000	3
CMCSA	A	10/2/2018	COMCAST A	1250000000	4000000000	3
CMCSA	A	10/2/2018	COMCAST A	1000000000	4000000000	3
CMCSA	A	10/2/2018	COMCAST A	500000000	4000000000	3
CI	A	9/6/2018	CIGNA A	3800000000	3800000000	4
CI	A	9/6/2018	CIGNA A	3100000000	3800000000	4
CI	A	9/6/2018	CIGNA A	3000000000	3800000000	4
CI	A	9/6/2018	CIGNA A	2200000000	3800000000	4
CI	A	9/6/2018	CIGNA A	1750000000	3800000000	4
CI	A	9/6/2018	CIGNA A	1250000000	3800000000	4
CI	A	9/6/2018	CIGNA A	1000000000	3800000000	4
CI	A	9/6/2018	CIGNA A	700000000	3800000000	4

Also attaching the example file with this change.

qlikwiz123 · ‎2019-05-20

Perfect. Thank you

Aggregate Rank() on a field

aggr

aggregate

Rank