# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Highlighted
Contributor

## 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

Tags (1)
1 Solution

Accepted Solutions
MVP

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

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,

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
14 Replies
Esteemed Contributor II

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

TRY

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

MVP

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

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,

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

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

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.

Contributor

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

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

MVP

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

Check 'Suppress Zero Values' on dimensions tab

Contributor

MVP

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

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?

MVP

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

Do you see this when you open the attached file?

Update: Used Jonathan's expression

Contributor

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

I see as above why?