topic Re: Need help in sorting top 5 values based on total in QlikView

Need help in sorting top 5 values based on total

Wed, 25 Jul 2012 20:06:54 GMT

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

Thu, 26 Jul 2012 07:43:49 GMT

Dear Ananad,

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

Thu, 26 Jul 2012 07:57:19 GMT

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

Thu, 26 Jul 2012 12:08:37 GMT

Thanks Lakhina !!!

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

Fri, 27 Jul 2012 09:38:39 GMT

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.