topic Re: Nested Aggr - Set Analysis in QlikView

Nested Aggr - Set Analysis

phoenix — Mon, 07 Jan 2019 21:58:38 GMT

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

Re: Nested Aggr - Set Analysis

sunny_talwar — Tue, 08 Jan 2019 13:49:27 GMT

@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

Re: Nested Aggr - Set Analysis

phoenix — Wed, 09 Jan 2019 13:20:22 GMT

@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)

Re: Nested Aggr - Set Analysis

sunny_talwar — Wed, 09 Jan 2019 11:59:42 GMT

Sundar? 🙂

Re: Nested Aggr - Set Analysis

phoenix — Wed, 09 Jan 2019 13:19:20 GMT

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