Nested Set Analysis with rank and aggr

[#1]

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)

[chematos]

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

[#3]

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 · Accepted solution]

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.

[#5]

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]

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