Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: Nested Aggr - Set Analysis

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

phoenix

Creator

2019-01-07
04:24 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 |

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 |

1,335 Views

1 Solution

Accepted Solutions

bgerchikov

Partner - Creator III

2019-01-07
07:00 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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)

7 Replies

bgerchikov

Partner - Creator III

2019-01-07
06:08 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!

1,324 Views

phoenix

Creator

2019-01-07
06:30 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,322 Views

bgerchikov

Partner - Creator III

2019-01-07
07:00 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2019-01-07
09:47 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 % | Balance | 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 ) | Due % | Balance |

LM | 3.64% | $1,050,053.35 | LM | 3.64% | $1,050,053.35 |

Fr | 2.53% | $1,581,308.10 | Fr | 2.53% | $1,581,308.10 |

Int | 2.20% | $1,105,440.39 | Int | 2.20% | $1,105,440.39 |

Ma | 2.13% | $3,310,626.70 | Ma | 2.13% | $3,310,626.70 |

Ti | 2.12% | $1,246,107.42 | Ti | 2.12% | $1,246,107.42 |

De | 2.10% | $1,249,319.67 | De | 2.10% | $1,249,319.67 |

Te | 2.09% | $7,297,799.99 | Te | 2.09% | $7,297,799.99 |

W | 2.06% | $3,703,667.27 | W | 2.06% | $3,703,667.27 |

Big | 2.05% | $49,213,667.16 | Big | 2.05% | $49,213,667.16 |

Ge | 2.01% | $1,648,498.29 | Ge | 2.01% | $1,648,498.29 |

Su | 1.95% | $93,547,731.32 | Su | 1.95% | $93,547,731.32 |

CMT | 1.88% | $4,924,117.17 | CMT | 1.88% | $4,924,117.17 |

Lo | 1.70% | $16,355,766.39 | Red | 1.70% | $1,550,989.61 |

Ca | 1.69% | $2,557,714.08 | Lo | 1.70% | $16,355,766.39 |

Lo | 1.68% | $1,301,617.45 | Ca | 1.69% | $2,557,714.08 |

Ga | 1.54% | $4,549,460.06 | Lo | 1.68% | $1,301,617.45 |

Tu | 1.53% | $1,872,888.40 | Ga | 1.54% | $4,549,460.06 |

Ho | 1.50% | $4,225,626.65 | Bo | 1.54% | $8,474,888.61 |

Br | 1.45% | $2,353,437.39 | Tu | 1.53% | $1,872,888.40 |

Vi | 1.45% | $1,317,374.95 | Bu | 1.52% | $1,997,419.22 |

AB | 1.44% | $1,085,893.87 | Ho | 1.50% | $4,225,626.65 |

St | 1.40% | $3,181,642.72 | Br | 1.48% | $7,890,247.90 |

Lak | 1.37% | $3,970,718.85 | PJ | 1.46% | $40,884,035.28 |

En | 1.35% | $1,941,699.58 | Bro | 1.45% | $2,353,437.39 |

Ap | 1.29% | $4,901,928.38 | Vi | 1.45% | $1,317,374.95 |

1,297 Views

phoenix

Creator

2019-01-07
10:06 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

__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

1,295 Views

bgerchikov

Partner - Creator III

2019-01-07
10:25 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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....

1,293 Views

bgerchikov

Partner - Creator III

2019-01-07
11:16 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,287 Views