Rank/ Aggr for Pivot table to get top 5

[rdsuperlike]

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

[vinieme12]

TRY

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

[jonathandienst · Accepted solution]

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,

[stabben23]

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]

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

[sunny_talwar]

Check 'Suppress Zero Values' on dimensions tab

[rdsuperlike]

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?

Update: Used Jonathan's expression

[rdsuperlike]

I see as above why?

I opened the qvw that u had attcahed in your reply