Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
We value your feedback – take our 5-minute QlikView modernization survey
cancel
Showing results for 
Search instead for 
Did you mean: 
rdsuperlike
Creator
Creator

Rank/ Aggr for Pivot table to get top 5

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

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Use expression:

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

Dimensions:

Fruits (vertical)

FY (horizontal)

Capture.PNG

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,

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

14 Replies
vinieme12
Champion III
Champion III

TRY

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
jonathandienst
Partner - Champion III
Partner - Champion III

Use expression:

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

Dimensions:

Fruits (vertical)

FY (horizontal)

Capture.PNG

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,

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
stabben23
Master
Master

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.

rdsuperlike
Creator
Creator
Author

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

Ranktest.png

sunny_talwar

Check 'Suppress Zero Values' on dimensions tab

rdsuperlike
Creator
Creator
Author

already tried that..doesnt work

sunny_talwar

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?

sunny_talwar

Do you see this when you open the attached file?

Capture.PNG

Update: Used Jonathan's expression

rdsuperlike
Creator
Creator
Author

Ranktest_2.png

I see as above why?

I opened the qvw that u had attcahed in your reply