topic Re: Nested Set Analysis with rank and aggr in QlikView

Nested Set Analysis with rank and aggr

Mon, 07 Jan 2013 19:28:57 GMT

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)

Re: Nested Set Analysis with rank and aggr

chematos — Mon, 07 Jan 2013 19:43:31 GMT

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

Re: Nested Set Analysis with rank and aggr

Mon, 07 Jan 2013 20:09:08 GMT

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)

Re: Nested Set Analysis with rank and aggr

chematos — Tue, 08 Jan 2013 11:44:11 GMT

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.

Re: Nested Set Analysis with rank and aggr

Wed, 09 Jan 2013 22:08:28 GMT

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

Re: Nested Set Analysis with rank and aggr

chematos — Thu, 10 Jan 2013 11:23:18 GMT

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