Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Nested Set Analysis with rank and aggr

Hi there,

I found a very helpful sample here that got me about 80% of the way to what I need but I'm having trouble with the last 20%.  I needed an expression that would work to just show the top 5 ranked clients by db size.  This worked out well but then I needed to add to the set analysis so that I would get those top 5 regardless of selections in the document and always for the last time that the information had been loaded.  I put the max date in a variable during the load and included the '1' disclaimers in the set analysis however it always seems to be impacted by other selections in the doc. 

Below is my latest non-working incarnation of the expression.  Not really sure where I'm going wrong here.

Thanks for any assistance with this.

Lorna

sum({1<[Client Name] = {"=rank(aggr(sum({1<DateType={'Metrics'},Date={'$vMaxUsageLoadDate'}>}dbSize),[Client Name]))<= 5"}>} dbSize)

1 Solution

Accepted Solutions
chematos
Specialist II
Specialist II

I can´t see what's wrong...

Have you tried to do in a straight table without rank() function?

For these cases I think is better going from easy to hard, step by step

Use this steps to make a top 5:

1) click on the chart properity.

2) go to presentation.

3) max number shown , set to 5.

View solution in original post

5 Replies
chematos
Specialist II
Specialist II

You should try using TOTAL:

sum({1<[Client Name] = {"=rank(aggr(sum({1<DateType={'Metrics'},Date={'$vMaxUsageLoadDate'}>}dbSize),[Client Name]))<= 5"}>}total dbSize)

or use empty filters of fields you want discard

sum({1<[Client Name] = {"=rank(aggr(sum({1<DateType={'Metrics'},Date={'$vMaxUsageLoadDate'}, FielfDiscard1=, FieldDiscard2=>}dbSize),[Client Name]))<= 5"}>}dbSize)

Hope this helps

Not applicable
Author

This for the suggestions.  Unfortunately I still can't get it to work.

Using total gave me an entire record set total ignoring the aggr grouping.

I have too many potential parameters to filter out the ones that I want to discard.  I did test it out by setting it up to exclude one filter but it didn't apply it when viewing the straight table.

Is there just something that I'm missing with regards to nesting the set statements?

Tried and didn't work:

sum({1<[Client Name] = {"=rank(aggr(sum({1}dbSize),[Client Name]))<= 5"}>} dbSize)

sum({1<[Client Name] = {"=rank(aggr({1}sum({1}dbSize),[Client Name]))<= 5"}>} dbSize)

sum({1<[Client Name] = {"=rank(aggr(sum({1<[Client Group]=}dbSize),[Client Name]))<= 5"}>} dbSize)

sum({1<[Client Name] = {"=rank(aggr({1<[Client Group]=}sum({1<[Client Group]=}dbSize),[Client Name]))<= 5"}>} dbSize)

sum({1<[Client Group]=,[Client Name] = {"=rank(aggr({1<[Client Group]=}sum({1<[Client Group]=}dbSize),[Client Name]))<= 5"}>} dbSize)

chematos
Specialist II
Specialist II

I can´t see what's wrong...

Have you tried to do in a straight table without rank() function?

For these cases I think is better going from easy to hard, step by step

Use this steps to make a top 5:

1) click on the chart properity.

2) go to presentation.

3) max number shown , set to 5.

View solution in original post

Not applicable
Author

Thanks Jose.

That's the route I ended up going and it's just fine.

I'll leave any other tweaking until we go to V11 and can use dimension limits etc.

Lorna

chematos
Specialist II
Specialist II

You're welcome, I'm glad to be helpfull