topic Re: Top Customer sorting in QlikView

Top Customer sorting

manojkulkarni — Tue, 25 Feb 2014 10:14:17 GMT

Hi everybody,

I need help in getting top customers from below table, where I would like to see Others in the bottom. Because Others consists of many small customers. Please any body provide me the better solution.

Name	Sales
Others	2600
Cust1	2500
Cust2	1200
Cust3	1000
Cust4	900
Cust5	700

Re: Top Customer sorting

amit_saini — Tue, 25 Feb 2014 10:18:52 GMT

Hi Manoj,

Hope this will help you:

rank(sum(Sales),1,1)

=aggr(if(Rank(sum(Sales))<=3,Sales),Sales) (For calculating Top 3)

=if(rank(sum(Sales),1,1)=1,'# 1')(For showing '#1' against top sales figure)

Re: Re: Top Customer sorting

MK_QSL — Tue, 25 Feb 2014 10:29:36 GMT

Like this?

Re: Top Customer sorting

MK_QSL — Tue, 25 Feb 2014 10:36:15 GMT

Manoj,

Check file in my second reply.

More dynamic way..

You need to hide the Rank column from presentation tab...

Re: Re: Top Customer sorting

amit_saini — Tue, 25 Feb 2014 10:37:34 GMT

Hi Manish,

I suggested him three different ways for calculating Top customer.

Is anything wrong????

Thanks,

Re: Top Customer sorting

MK_QSL — Tue, 25 Feb 2014 10:40:42 GMT

I think you don't understand question.

He wants whenever he sort the sales from top to bottom (Descending Order) the SUM(Sales) for Others should go to the bottom.

Hope now it's clear to you.

Re: Top Customer sorting

amit_saini — Tue, 25 Feb 2014 10:44:16 GMT

Thanks for correcting me. I misunderstood the requirement.

Thanks,

Re: Top Customer sorting

aveeeeeee7en — Tue, 25 Feb 2014 10:45:01 GMT

Hi Manoj

Use Rank Function to do Customer wise Sorting

Use this Expression in your Script:

=if(aggr(rank(sum(Sales)),Sales)<=10,Sales)

Regards

Aviral Nag

Re: Top Customer sorting

MK_QSL — Tue, 25 Feb 2014 10:52:04 GMT

My pleasure... !! Welcome to the QlikWorld

Re: Top Customer sorting

amit_saini — Tue, 25 Feb 2014 10:56:32 GMT

Yes Brother, I'm new here

Re: Top Customer sorting

manojkulkarni — Tue, 25 Feb 2014 11:09:03 GMT

Thanks for your replies & solution. But this will not work for my problem.

As mentioned earlier, my data is like below

Name	Sales
Others	2600
Cust1	2500
Cust2	1200
Cust3	1000
Cust4	900
Cust5	700

"Others" is one of the customer name, having clubbed small customers @ data load itself. In chart I want to display actual customers first & later at the bottom merged one's. By default, Others is coming on top because of the highest sales value. But some how i want that to be at bottom. Is there way to sort all customers on there value descending except "Others"

Re: Top Customer sorting

MK_QSL — Tue, 25 Feb 2014 11:22:31 GMT

Like this?

Re: Top Customer sorting

MK_QSL — Tue, 25 Feb 2014 11:27:18 GMT

CHECK THIS ALSO...

Re: Top Customer sorting

Thu, 27 Mar 2014 16:19:10 GMT

Please refer this post : Qlikview Bitz and Pieces: Custom Sort in Qlikview

Re: Top Customer sorting

maxgro — Thu, 27 Mar 2014 22:54:57 GMT

I think in sort tab

sort by expression

descending

pick(wildmatch(Name, 'Others', '*'), 'last', sum(Sales))

logic is: sort descending

if Name not 'Others' by sum(Sales)

if name = 'Others' put at the end ('last' or some other string)