topic Re: top 5 function in dimension in QlikView

top 5 function in dimension

Thu, 08 Sep 2011 07:49:59 GMT

Hi,

I have a problem with a rank function. I would like to see a top 5 of reportsubtype based on the last 12 months. With the restriction of reporttype={'Stilstand'}

In my dimension I have:

=if(aggr(rank(sum({1<Park = P({$}), Code = P({$}),Date = {">=$(=(addmonths(max(Date),-12))+1)<=$(=(max(Date)))"}, ReportType={'STILSTAND'}>}Delta)),ReportSubType)<5,ReportSubType)

for example:

Park Code reportsubtype Delta

company 1 1 product A 100

1 product B 70

1 product C 50

1 product D 40

1 product E 20

Now I have the problem that I only see 4 items and when I select company 1 my top 5 ranking changed. Then I see 5 records. So the problem is that the ranking doesn't show the right thinks.

Can anybody help me?

thanks a lot

top 5 function in dimension

Thu, 08 Sep 2011 08:59:34 GMT

I think the test '<5' explains why you are only returning 4.

Its quite a complicated statement and I suggest you break it down and build it up reviewing the results of each step. For example, the tests on 'Park' and 'Code' are using the 'P'osiible functions but do you really mean the $ set not 1 which is the set you specify in the 'sum'? Do you need to addmonths -12 then +1 when addmonths -11 might be clearer?

Hope this helps - good luck!

Gordon

Re: top 5 function in dimension

Thu, 08 Sep 2011 12:14:08 GMT

Maybe it's more simple using a straight table sorted on your ranking expression and joking with "max number" box in presentation tab.

You can use an expression also so you could let the user choose his presonal top.

Stefano.