Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
rdsuperlike
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
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)

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

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

stabben23
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.

rdsuperlike
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

Ranktest.png

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

Check 'Suppress Zero Values' on dimensions tab

rdsuperlike
Contributor

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

already tried that..doesnt work

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?

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

Do you see this when you open the attached file?

Capture.PNG

Update: Used Jonathan's expression

rdsuperlike
Contributor

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

Ranktest_2.png

I see as above why?

I opened the qvw that u had attcahed in your reply