Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
A | 1.08% | $1,307,394,657.97 |
B | 1.21% | $744,059,134.25 |
C | 0.77% | $357,608,576.29 |
D | 0.64% | $254,258,526.58 |
E | 0.72% | $225,546,921.52 |
F | 0.70% | $214,473,494.20 |
G | 1.25% | $208,853,674.18 |
H | 0.73% | $140,247,509.05 |
I | 0.90% | $136,058,009.82 |
J | 0.96% | $104,991,278.33 |
K | 0.59% | $99,152,698.37 |
L | 1.95% | $93,547,731.32 |
M | 0.60% | $63,879,678.08 |
N | 2.05% | $49,213,667.16 |
O | 0.54% | $48,322,095.78 |
P | 1.46% | $40,884,035.28 |
Q | 0.83% | $39,308,599.38 |
R | 1.03% | $38,115,956.84 |
S | 0.49% | $34,866,485.54 |
T | 0.63% | $33,443,292.79 |
U | 0.32% | $26,726,725.36 |
Top 10 due % Products(Balance > 25M) | ||
????? | Due% | Balance |
N | 2.05% | $49,213,667.16 |
L | 1.95% | $93,547,731.32 |
P | 1.46% | $40,884,035.28 |
G | 1.25% | $208,853,674.18 |
B | 1.21% | $744,059,134.25 |
A | 1.08% | $1,307,394,657.97 |
R | 1.03% | $38,115,956.84 |
J | 0.96% | $104,991,278.33 |
I | 0.90% | $136,058,009.82 |
Q | 0.83% | $39,308,599.38 |
@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 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
@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)
Sundar? 🙂
@sunny_talwar oops, thought I selected your name. I was half awake replying to the post last night 🙂 Thanks for your help as always