Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER

Top 5 analysis using Rank fuction

Champion
Champion

Top 5 analysis using Rank fuction

We could use Rank function to find top 5 Record.

suppose we have Product wise sales below and we want to analysis of top 5 product Sales.then using Bar chart we can easily limit the dimension and achieve. but if we want in Pivot or straight table. it is not easy to find even using limit dimension.

Using rank function we could achive it easily

Raw Data:

 

rank of Sales

 

and top 5 sales by using

 

 

see the ached files for more detail

Comments
Champion
Champion

I forgot to mention. attched example also covers bottom 5 records using Rank

0 Likes
Partner
Partner

Hi,

I have a doubt related to rank.

As you can see in the below snapshot, the coke brand top 10 is correct but for Pepsi brand Rank 7 is missing and the 7th rank should be the "regular new" as its sales is less than "java and vanilla"

How to achieve the missing 7th rank or what expression i should use ???

Regards,

Kavita

0 Likes
Champion
Champion

Could you please share the sample data. as I can see  Java and vanila have same value. so it should be at 6 but i am not sure about your No , how you are taking ?

0 Likes
Partner
Partner

Yes ur right Java and vanilla have same values so it is rank as 6th, but what about 7th rank?

Ideally if you see in the left table  "regular new" value is less than java and vanilla so it should be in 7th rank but the 7th rank is missing and it is ranked as 8th.

This is my doubt.

I am unable to attached the qvw.

0 Likes
Champion
Champion

are you reffering No as Rank  if so what is the expression for no

0 Likes
Partner
Partner

Aggr(Rank function help needed:Top 10 "for each Brand" wanted Not Total top 10 in Pivot Table

Here you can find the QVW, but the marked correct answer  doesn't solve my issue.

0 Likes

A nice clear explanation to introduce Rank & Aggr.

It would be better if the sample Sales data in screen shots was not already sorted in order.

It is worth mentioning that you can also rank data using Dimension Limits.

With Dimension Limits you can only rank data based on the first dimension, but it is simpler to use.

Using Dimension Limits means you do not need to use the Rank function or Aggr in the chart expression.

Champion
Champion

Hi kavita ,

Remove dimension No

and add

for your query

use expression

rank(total

aggr(sum([2011 Sales]),Flavor)

)

hope this solve your query

Champion
Champion

Thanks Colin for your suggestion.

0 Likes
Master II
Master II

Your attachment is not showing Rank related info.Please check and upload right one. Thank you.

Version history
Revision #:
2 of 2
Last update:
‎2019-06-21 06:13 AM
Updated by:
 
Contributors