topic Re: Aggregate Rank() on a field in QlikView

Aggregate Rank() on a field

qlikwiz123 — Sat, 16 Nov 2024 03:33:21 GMT

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

Re: Aggregate Rank() on a field

Nicole-Smith — Thu, 16 May 2019 17:18:13 GMT

The following works as a dimension:

Re: Aggregate Rank() on a field

qlikwiz123 — Thu, 16 May 2019 17:59:43 GMT

@Nicole-Smith

Hi,

Please find the Attached QVW. The same is not working on my end.

Re: Aggregate Rank() on a field

Nicole-Smith — Thu, 16 May 2019 19:01:00 GMT

It's working properly based on the sum of Funds per Cat (I've updated the chart in your example file to contain the sum of Funds by Cat as an expression).

Do you maybe want it grouped by Giver instead of Cat? I've added a second chart that does that.

Re: Aggregate Rank() on a field

qlikwiz123 — Thu, 16 May 2019 19:02:38 GMT

@Nicole-Smith Thanks you so much. Let me check this one

Re: Aggregate Rank() on a field

qlikwiz123 — Thu, 16 May 2019 19:24:45 GMT

Re: Aggregate Rank() on a field

qlikwiz123 — Thu, 16 May 2019 19:23:56 GMT

@Nicole-Smith

Please look at the attached Excel Source file. Here, you can clearly see that the Highest to lowest Funds with respect to Cat, the order is different. I have added a Rank column manually to help you understand how my rankings should look like.

1. How do I get them to show same rank if the Funds value is same instead of multiple numbers like below?

2. This is how my Ranks should look like, if you look at the Excel file sorted based on Funds from highest to lowest.

Re: Aggregate Rank() on a field

Nicole-Smith — Thu, 16 May 2019 22:17:53 GMT

If grouping by Cat, JPM totals to more than CVS (as seen in the first chart in my previous example file--second to last column). I'm not sure what your Excel file is doing to ignore JPM. From the totals on that first chart, I can see that the ranking function that I wrote is working properly.

If you want it to show the values without the dashes, you can change the extra parameters of the Rank function to fit your needs. I usually go with 1 and 1 but other options can be found here. For example:
=Aggr(Rank(aggr(Sum(Funds),Cat), 1, 1),Cat)

Re: Aggregate Rank() on a field

qlikwiz123 — Fri, 17 May 2019 13:46:36 GMT

If you look at the Excel file attached or even the screenshot, JPM is not the highest Funds, it is the CVS and then ABIBB and CMCSA and so on....

Re: Aggregate Rank() on a field

Nicole-Smith — Fri, 17 May 2019 14:38:02 GMT

You're right--the sum I have isn't working properly. If I can figure out why, I can fix this rank function...

Re: Aggregate Rank() on a field

Nicole-Smith — Fri, 17 May 2019 14:45:30 GMT

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

Re: Aggregate Rank() on a field

qlikwiz123 — Mon, 20 May 2019 13:49:24 GMT

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

Re: Aggregate Rank() on a field

Nicole-Smith — Fri, 17 May 2019 22:05:31 GMT

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.

Re: Aggregate Rank() on a field

qlikwiz123 — Mon, 20 May 2019 13:48:56 GMT

Perfect. Thank you