Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
phoenix
Creator
Creator

Nested Aggr - Set Analysis

I need to get the top 10 highest Due % products (only products with balance >$25M) in the portfolio for a specific month.

I am able to get to the top 10 highest Due % products (for all products) using 

=if(aggr(rank(sum({<Date={20181231}>}Due)/sum({<Date={20181231}>}UNPD_PRIN_BAL_AMT)),Product)<=10,Product)

but cannot figure out how to do the aggr(rank()) on the subset of products with balance >$25M for that specific month

Products with Balance > 25M  
=if(aggr(sum({<Date={20181231}>}Balance),Product)>25000000,Product)Due%Balance
1.08%$1,307,394,657.97
B1.21%$744,059,134.25
C0.77%$357,608,576.29
D0.64%$254,258,526.58
E0.72%$225,546,921.52
0.70%$214,473,494.20
G1.25%$208,853,674.18
0.73%$140,247,509.05
0.90%$136,058,009.82
J0.96%$104,991,278.33
K0.59%$99,152,698.37
L1.95%$93,547,731.32
M0.60%$63,879,678.08
N2.05%$49,213,667.16
O0.54%$48,322,095.78
P1.46%$40,884,035.28
Q0.83%$39,308,599.38
R1.03%$38,115,956.84
S0.49%$34,866,485.54
T0.63%$33,443,292.79
U0.32%$26,726,725.36
   
Top 10 due % Products(Balance > 25M)   
?????Due%Balance
N2.05%$49,213,667.16
L1.95%$93,547,731.32
P1.46%$40,884,035.28
G1.25%$208,853,674.18
B1.21%$744,059,134.25
1.08%$1,307,394,657.97
R1.03%$38,115,956.84
J0.96%$104,991,278.33
0.90%$136,058,009.82
Q0.83%$39,308,599.38
1 Solution

Accepted Solutions
sunny_talwar


@phoenix wrote:

 

but cannot figure out how to do the aggr(rank()) on the subset of products with balance >$25M for that specific month


What are you not able to figure out? Do you have a sample you can share to help you better here

View solution in original post

4 Replies
sunny_talwar


@phoenix wrote:

 

but cannot figure out how to do the aggr(rank()) on the subset of products with balance >$25M for that specific month


What are you not able to figure out? Do you have a sample you can share to help you better here

phoenix
Creator
Creator
Author

@sunny_talwar I was able to solve using this expression

 

=if(aggr(rank(TOTAL sum({<Date={20181231},Product={"=sum({<Date={20181231}>}Balance)>25000000"}>}Due)/sum({<Date={20181231},Product={"=sum({<Date={20181231}>}Balance)>25000000"}>}Balance)),Product)<= 10,Product)

sunny_talwar

Sundar? 🙂

phoenix
Creator
Creator
Author

@sunny_talwar oops, thought I selected your name. I was half awake replying to the post last night 🙂 Thanks for your help as always