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 |
