topic Re: Rank/ Aggr for Pivot table to get top 5 in QlikView

Rank/ Aggr for Pivot table to get top 5

rdsuperlike — Mon, 30 Jan 2017 08:11:52 GMT

I want Top 5 fruits for 2016 and 2017 to be listed in the uploaded qvw. If Top 5 of both year is not possible then I want top 5 fruits by 2017

I tried using dimensions limit by making it straight table. But then when i try to move the Fiscal year to the top column by moving blue arrow I cant. So I used pivot table, but in pivot table the dimension limits go away.

I think I need to use aggr function with rank function, but not sure how.

Can someone help me here?

Thanks once again

Re: Rank/ Aggr for Pivot table to get top 5

vinieme12 — Mon, 30 Jan 2017 08:32:33 GMT

TRY

=if(aggr(rank(Count(Fruits),1,1),Fruits)<=5,Fruits)

Re: Rank/ Aggr for Pivot table to get top 5

jonathandienst — Mon, 30 Jan 2017 08:35:55 GMT

Use expression:

=If(Rank(Count({<FY = {2016,2017}>} [Customer ID]), 1, 1) < 5, Count({<FY = {2016,2017}>} [Customer ID]))

Dimensions:

Fruits (vertical)

FY (horizontal)

You get more than 5 values because of the ties - for example the 3 instances of value 2 in 2016 are all Rank 4.

PS - I replaced the calculated dimension with the field FY and used the set expression to filter the years,

Re: Rank/ Aggr for Pivot table to get top 5

stabben23 — Mon, 30 Jan 2017 08:36:37 GMT

if(num(aggr(Rank(Count({<FY={'2017'}>}[Customer ID])),Fruits)<=5),Count({<FY={'2017'}>}[Customer ID]))

if(num(aggr(Rank(Count({<FY={'2016'}>}[Customer ID])),Fruits)<=5),Count({<FY={'2016'}>}[Customer ID]))

Hi, try above, Remove FY from Dimensions, add 2 expressions, one for each year 2016 and 2017.

This is very much hardcoded, but you can change the year against variables instead.

Re: Rank/ Aggr for Pivot table to get top 5

rdsuperlike — Mon, 30 Jan 2017 20:01:17 GMT

this looks good but how do I use suppress nulls. I clicked on suppress null checkbox but still see this

Re: Rank/ Aggr for Pivot table to get top 5

sunny_talwar — Mon, 30 Jan 2017 20:11:42 GMT

Check 'Suppress Zero Values' on dimensions tab

Re: Rank/ Aggr for Pivot table to get top 5

rdsuperlike — Mon, 30 Jan 2017 20:21:45 GMT

already tried that..doesnt work

Re: Rank/ Aggr for Pivot table to get top 5

sunny_talwar — Mon, 30 Jan 2017 20:24:12 GMT

Do you have more than one expression? I don't see why it should not suppress those if you have Suppress Zero Values on presentation tab checked if you only have one expression?

Re: Rank/ Aggr for Pivot table to get top 5

sunny_talwar — Mon, 30 Jan 2017 20:25:45 GMT

Do you see this when you open the attached file?

Update: Used Jonathan's expression

Re: Rank/ Aggr for Pivot table to get top 5

rdsuperlike — Mon, 30 Jan 2017 20:37:41 GMT

I see as above why?

I opened the qvw that u had attcahed in your reply

Re: Rank/ Aggr for Pivot table to get top 5

sunny_talwar — Mon, 30 Jan 2017 20:43:01 GMT

Could be a version related issue. What version of QV you are using?

Re: Rank/ Aggr for Pivot table to get top 5

rdsuperlike — Mon, 30 Jan 2017 20:45:09 GMT

11.00.11282.0 SR1 64-bit Edition

Re: Rank/ Aggr for Pivot table to get top 5

sunny_talwar — Mon, 30 Jan 2017 20:47:00 GMT

Wow, you are still using SR1? Can you update to a more recent version of QV may be?

Re: Rank/ Aggr for Pivot table to get top 5

rdsuperlike — Mon, 30 Jan 2017 21:03:31 GMT

OMG..u are right...i just tested this one someone else's laptop who has later version and looked good there....how do you upgrade? or rather will check with IT team at my company