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: 
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
bgerchikov
Partner - Creator III
Partner - Creator III

I see.

Here is another option that works for me:

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

View solution in original post

7 Replies
bgerchikov
Partner - Creator III
Partner - Creator III

Try this one for dimension:

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

Good Luck!

phoenix
Creator
Creator
Author

tried this but it is doing aggr(rank(TOTAL sum({<Date={20181231}>}Due)/sum({<Date={20181231}>}UNPD_PRIN_BAL_AMT)),Product)<= 10 and then filtering Products for >$25M. i want it to filter the products with $25M first and then do the Due % rank
bgerchikov
Partner - Creator III
Partner - Creator III

I see.

Here is another option that works for me:

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

phoenix
Creator
Creator
Author

I thought it is working but I am getting different results between

=if(aggr(rank(sum({<Date={20181231},Product={"=sum({<Date={20181231}>}Balance)>1000000"}>}Due)/sum({<Date={20181231},Product={"=sum({<Date={20181231}>}Balance)>1000000"}>}Balance)),Product)<= 25,Product)

and

=if(aggr( sum( {< Date={20181231},Product={"=sum({<Date={20181231}>}Balance)>1000000"} >} Due ) /sum( {< Date={20181231},Product={"=sum({<Date={20181231}>}Balance)>1000000"} >} Balance ) ,Product )>aggr( sum(total {< Date={20181231} >} Due ) /sum(total {< Date={20181231} >} Balance ) ,Product ) ,Product ) 

 highlighted in BOLD are additional results I am getting from 2nd set analysis which i dont see in the first one

if(aggr(rank(sum({<Date={20181231},Product={"=sum({<Date={20181231}>}Balance)>1000000"}>}Due)/sum({<Date={20181231},Product={"=sum({<Date={20181231}>}Balance)>1000000"}>}Balance)),Product)<= 25,Product)Due %Balanceif(aggr( sum( {< Date={20181231},Product={"=sum({<Date={20181231}>}Balance)>1000000"} >} Due ) /sum( {< Date={20181231},Product={"=sum({<Date={20181231}>}Balance)>1000000"} >} Balance ) ,Product ) > aggr( sum(total {< Date={20181231} >} Due ) /sum(total {< Date={20181231} >} Balance ) ,Product ) ,Product ) Due %Balance
LM3.64%$1,050,053.35LM3.64%$1,050,053.35
Fr2.53%$1,581,308.10Fr2.53%$1,581,308.10
Int2.20%$1,105,440.39Int2.20%$1,105,440.39
Ma2.13%$3,310,626.70Ma2.13%$3,310,626.70
Ti2.12%$1,246,107.42Ti2.12%$1,246,107.42
De2.10%$1,249,319.67De2.10%$1,249,319.67
Te2.09%$7,297,799.99Te2.09%$7,297,799.99
W2.06%$3,703,667.27W2.06%$3,703,667.27
Big2.05%$49,213,667.16Big2.05%$49,213,667.16
Ge2.01%$1,648,498.29Ge2.01%$1,648,498.29
Su1.95%$93,547,731.32Su1.95%$93,547,731.32
CMT1.88%$4,924,117.17CMT1.88%$4,924,117.17
Lo1.70%$16,355,766.39Red1.70%$1,550,989.61
Ca1.69%$2,557,714.08Lo1.70%$16,355,766.39
Lo1.68%$1,301,617.45Ca1.69%$2,557,714.08
Ga1.54%$4,549,460.06Lo1.68%$1,301,617.45
Tu1.53%$1,872,888.40Ga1.54%$4,549,460.06
Ho1.50%$4,225,626.65Bo1.54%$8,474,888.61
Br1.45%$2,353,437.39Tu1.53%$1,872,888.40
Vi1.45%$1,317,374.95Bu1.52%$1,997,419.22
AB1.44%$1,085,893.87Ho1.50%$4,225,626.65
St1.40%$3,181,642.72Br1.48%$7,890,247.90
Lak1.37%$3,970,718.85PJ 1.46%$40,884,035.28
En1.35%$1,941,699.58Bro1.45%$2,353,437.39
Ap1.29%$4,901,928.38Vi1.45%$1,317,374.95
phoenix
Creator
Creator
Author

Set Analysis 1:

=if(aggr(rank(sum({<Date={20181231},Product={"=sum({<Date={20181231}>}Balance)>1000000"}>}Due)/sum({<Date={20181231},Product={"=sum({<Date={20181231}>}Balance)>1000000"}>}Balance)),Product)<= 25,Product)

Set Analysis 2:

=if(aggr( sum( {< Date={20181231},Product={"=sum({<Date={20181231}>}Balance)>1000000"} >} Due ) /sum( {< Date={20181231},Product={"=sum({<Date={20181231}>}Balance)>1000000"} >} Balance ) ,Product )>aggr( sum(total {< Date={20181231} >} Due ) /sum(total {< Date={20181231} >} Balance ) ,Product ) ,Product ) 

Set Analysis 3: (two dimensions)

=Product

=if(Date=20181231,Date)

Set Analysis 4:

=if(aggr(rank(sum({<Date={20181231},Product={"=sum({<Date={20181231}>}Balance)>1000000"}>}Due)/sum({<Date={20181231},Product={"=sum({<Date={20181231}>}Balance)>1000000"}>}Balance)),Product,Date)<= 25,Product)

Out of these Four straight tables I am seeing more results from set Analysis 3,4 and they both match. Not sure why first two are not working. I can use Set analysis 4, but rank is not working in that. It is giving me all results for (>$1M) instead of top 25. how to make Rank function work in the Set analysis 4? Thanks

bgerchikov
Partner - Creator III
Partner - Creator III

Hi Phoenix,

Not sure about your second expression. For example, Product "Lo" appears 2 times. 

Can you upload your app?

Sorry, I'm away from my testing environment right now....

 

bgerchikov
Partner - Creator III
Partner - Creator III

OK. Let's try to go in different direction.

For dimension you will use your initial Aggr statement, which limit your products by 25M or so.

In chart expression you can use the rank function

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