Finding Top 5 products in Qlik Sense.

[ranidas]

Dear Team,
I am new to Qlik sense , just one month experience.
I have a requirement as below .
Need to show Top 5  products based on Good projects count .Structure is exactly similar as attached.

If more than top 5 products are present , Sum all the remaining Good projects count and show in seperate column name as "Others" and selected top 5 needs to be shown in a alphabetic order in visualization and others column should always be positioned last.

In case if, 5  or all products has similar count ,need to pick the top5 products which comes alphabetically  first  which is not happening .

Sort by expression, alphebetically,numerically tried , but could not found the desired output.

Also tried with Limitation property to restrict top 5.

My Rank expression : 

Rank Exp:

Aggr(IF(Num(Rank(sum(Good Projects),4))<=5,Products,'Others'),Products)

Please find the screen shots of dummy data and kindly help!.

[sunny_talwar · Accepted solution]

Try this

=Aggr(If(Num(Rank(Sum([Good Projects])+Rank(Product)/1000, 4)) <=5, Product, 'Others'), Product)

[ranidas · Accepted solution]

Dear Sunny,

Thank you for your prompt response.
Your suggested solution is working perfectly for my requirement .
Requesting you to please provide the explination, as i have tried analysing it by breaking given expression in to chunks but could not understand the same :(.
Your explination would really helpful for beginners like me . 

and Also wanted to know as attached  ,

1)when we are showing alpabetically Qlik gives 1st priority to special characters,2nd to alphanumeric then alphabets ?
2)To place Others always at last position , i have checked below sorting.

Sort by expression:
Aggr(Num(Rank(Sum(Good Projects)+Rank(Products)/1000,4))<=5, Products)

and Sort by Alphabetically: Ascending

[Michael_Tarallo]

Hi Rani - check out this video for AGGR() to start: https://www.youtube.com/watch?v=-yPpuxE_Y0w - this will help you understand the basics of AGGR(). 

[sunny_talwar]

So, basically I am not just ranking based on Sum([Good Projects]), but also the Rank(Product)/1000 (Division by 1000 is to just to make sure that the sorting is first done by Sum, but if two sums are the same, then Rank of Product is checked). I guess this is where a slight complication comes where I am guessing special characters might be ranked higher than to other things. I guess you can create a own mapping in the script to give your product a rank based on the name instead of using QlikView's Rank().

[ranidas]

Hi Michael,
Thank you for suggesting this Video. I did Watched and helpful to me.

[ranidas]

Hi Sunny,
Thank you for your explination . 
I have understood it and analysed on data. Kudos to you.
so basically here Rank(Product)/1000, division by 100,10  instead of 1000 will also  work, whereas if we multiply with 10,100  would give incorrect results in case of picking top 5.

-As we have dynamic products and we cannot have any control on number of products to create a own mapping in script.
Kindly let me know in case of any suggestions.