Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikwiz123
Creator III
Creator III

Aggregate Rank() on a field

 

Please look at the screenshot below.

Capture.JPG

 

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

Labels (3)
13 Replies
Nicole-Smith

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) 
JPMB7/16/2018JPMORGAN CHASE & CO22500000002403750000001
JPMB11/28/2018JP MORGAN CHASE BANK NA25000000002403750000001
JPMB1/16/2018JPMORGAN CHASE & CO22500000001403750000001
JPMB1/16/2018JPMORGAN CHASE & CO17500000001403750000001
JPMB1/29/2018JP MORGAN CHASE BANK NA12500000001403750000001
JPMB1/29/2018JP MORGAN CHASE BANK NA10000000001403750000001
JPMB2/8/2018JP MORGAN CHASE BANK NA17500000001403750000001
JPMB4/16/2018JPMORGAN CHASE & CO22500000001403750000001
JPMB4/16/2018JPMORGAN CHASE & CO17500000001403750000001
JPMB4/16/2018JPMORGAN CHASE & CO5000000001403750000001
JPMB4/19/2018JP MORGAN CHASE BANK NA22000000001403750000001
JPMB4/19/2018JP MORGAN CHASE BANK NA13000000001403750000001
JPMB6/11/2018JPMORGAN CHASE & CO14000000001403750000001
JPMB6/11/2018JPMORGAN CHASE & CO11000000001403750000001
JPMB7/16/2018JPMORGAN CHASE & CO10000000001403750000001
JPMB7/18/2018JPMORGAN CHASE & CO (TAP)2500000001403750000001
JPMB8/23/2018JPMORGAN CHASE BANK NA12500000001403750000001
JPMB9/17/2018JPMORGAN CHASE & CO14750000001403750000001
JPMB10/12/2018JP MORGAN CHASE BANK NA8000000001403750000001
JPMB1/16/2019JP MORGAN CHASE & CO18500000001403750000001
JPMB1/22/2019JP MORGAN CHASE & CO20000000001403750000001
JPMB2/12/2019JP MORGAN CHASE BANK15000000001403750000001
JPMB3/15/2019J.P. MORGAN CHASE & CO.22500000001403750000001
CVSA3/6/2018CVS HEALTH A50000000002400000000002
CVSA3/6/2018CVS HEALTH A10000000002400000000002
CVSA3/6/2018CVS HEALTH A90000000001400000000002
CVSA3/6/2018CVS HEALTH A80000000001400000000002
CVSA3/6/2018CVS HEALTH A60000000001400000000002
CVSA3/6/2018CVS HEALTH A30000000001400000000002
CVSA3/6/2018CVS HEALTH A20000000001400000000002
qlikwiz123
Creator III
Creator III
Author

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

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) 
CVSA3/6/2018CVS HEALTH A900000000090000000001
CVSA3/6/2018CVS HEALTH A800000000090000000001
CVSA3/6/2018CVS HEALTH A600000000090000000001
CVSA3/6/2018CVS HEALTH A500000000090000000001
CVSA3/6/2018CVS HEALTH A300000000090000000001
CVSA3/6/2018CVS HEALTH A200000000090000000001
CVSA3/6/2018CVS HEALTH A100000000090000000001
ABIBBA3/20/2018ANHEUSER-BUSCH INBEV WOR250000000042500000002
ABIBBA3/20/2018ANHEUSER-BUSCH INBEV WOR150000000042500000002
ABIBBA3/20/2018ANHEUSER-BUSCH INBEV WOR50000000042500000002
ABIBBA1/10/2019ANHEUSER-BUSCH CO/INBEV425000000042500000002
ABIBBA1/10/2019ANHEUSER-BUSCH CO/INBEV400000000042500000002
ABIBBA1/10/2019ANHEUSER-BUSCH CO/INBEV250000000042500000002
ABIBBA1/10/2019ANHEUSER-BUSCH CO/INBEV200000000042500000002
ABIBBA1/10/2019ANHEUSER-BUSCH CO/INBEV75000000042500000002
CMCSAA2/1/2018COMCAST A120000000040000000003
CMCSAA2/1/2018COMCAST A100000000040000000003
CMCSAA10/2/2018COMCAST A400000000040000000003
CMCSAA10/2/2018COMCAST A300000000040000000003
CMCSAA10/2/2018COMCAST A250000000040000000003
CMCSAA10/2/2018COMCAST A200000000040000000003
CMCSAA10/2/2018COMCAST A175000000040000000003
CMCSAA10/2/2018COMCAST A150000000040000000003
CMCSAA10/2/2018COMCAST A125000000040000000003
CMCSAA10/2/2018COMCAST A100000000040000000003
CMCSAA10/2/2018COMCAST A50000000040000000003
CIA9/6/2018CIGNA A380000000038000000004
CIA9/6/2018CIGNA A310000000038000000004
CIA9/6/2018CIGNA A300000000038000000004
CIA9/6/2018CIGNA A220000000038000000004
CIA9/6/2018CIGNA A175000000038000000004
CIA9/6/2018CIGNA A125000000038000000004
CIA9/6/2018CIGNA A100000000038000000004
CIA9/6/2018CIGNA A70000000038000000004

 

Also attaching the example file with this change.

qlikwiz123
Creator III
Creator III
Author

Perfect. Thank you