# Need help in sorting top 5 values based on total

Hi

I'm Anand  and  am very new to qlikview , so i request you guys to help me out from this challenge

overview:

In the qlikview and excel document attached there are "departments" as a,b,c,d, and their quarter sales in the tenure or quarter mentioned above

and the last total column is the performance and sum of all quarters

challenge is :

I need a table chart which can display the result of top performers based on the total and should display only the top 5 departments and the remaining departments should be included in "all others " which comes after the top 5 performers as shown in the excel image  attached .

what expression should be used to solve this challenge and where i should use that expression.

kindly help!!!!!!!!!!

• ###### Re: Need help in sorting top 5 values based on total

Please have a look at the file attached for a solution.

I believe you need a claulated dimension like below:

aggr(if((rank(sum( Sales)))<=5,Department,'All Others'),Department)

Best of luck!

Regards

Rahul

• ###### Re: Need help in sorting top 5 values based on total

And to make 'All Others' be at the bottom, please change sort expression to below:

If(rank(sum(Total <Department> Sales))=1,7,rank(sum(Total <Department> Sales)))

Regards

Rahul

• ###### Re: Need help in sorting top 5 values based on total

Thanks Lakhina !!!

• ###### Re: Need help in sorting top 5 values based on total

hi anand,

You can use sort on total as descending order removing sort on all other dimension and promote total to top.now restrict max no. in prestentation tab to 6.after that go to dimension limit tab, check on limits box where you can restrict your top 5 values as largest.below that check on show other option.total of all other can be displayed using this option.This dimension limit option is available in qlikview 11 only.