topic Re: TOP5 Customers based on sales in QlikView

TOP5 Customers based on sales

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I want to display Top5 customers based on sales in straight table by using expression.

Only Top5 customers (Based on sales) should display, remaining customers should not display.

Please drop the solution here only as i can't open QVW file in my machine.

Customer	Sales
Alles Lusekofter	63.55
Art et Fashion	140.4
Aujourd´hui	343.44
Autokleider	205.2
Belgium Black	4048
Big Foot Shoes	95.9
Bobby Socks	163.36
Boleros	3931.22
Bond Ltd	21.36
Boombastic	185.2
Casual Clothing	332.64
Champes	182
Chateau de Ville	620.55
Cloe do Pau	3189.06
Copacabana	165.6
Da Bikini Expertu	796.4
Da Santho	2433.9
Das Alpen Shoe	77.97
Davenport Fash	617.4

Re: TOP5 Customers based on sales

alexandros17 — Fri, 20 Oct 2017 13:16:08 GMT

Suppose you have this table:

Invoice with Customer and Amount Field

Every Database has an instruction to exctract the top n values (There are "Limit" or "TOP" Keawords for instance)

a query like this one:

select customer, sum(Amount) as somma from invoice group by customer order by somma desc limit 5

extract the first 50 then do an inner join of this one with the complete table of invoice (invoice is obviously for example)

Re: TOP5 Customers based on sales

pradosh_thakur — Fri, 20 Oct 2017 13:20:41 GMT

IF want to do it in dashboard itself

1: use Rank function to filter out first 5 then filter

2: sort it by sales descending . use if(rowno()<=5,sum(sales)) in expression ans suppress null

3: dimesnsion limits -> show top 5 values

regards

Pradosh

Re: TOP5 Customers based on sales

Mark_Little — Fri, 20 Oct 2017 13:25:36 GMT

Hi.

If you are using QlikView.

Create your Straight table, with the expressions SUM(Sales).

On the Dimension Limits,

set up as pictures below.

Mark

Re: TOP5 Customers based on sales

Anonymous — Fri, 20 Oct 2017 14:11:10 GMT

Hi,

If I use if(rowno()<=5,sum(sales)), the result is showing like below.

Re: TOP5 Customers based on sales

pradosh_thakur — Fri, 20 Oct 2017 14:17:13 GMT

not like this ..share the app if possible .

regards

Pradosh

Re: TOP5 Customers based on sales

Mark_Little — Fri, 20 Oct 2017 14:25:48 GMT

Here is a example attached.

Re: TOP5 Customers based on sales

Anonymous — Fri, 20 Oct 2017 14:46:35 GMT

Please provide the correct expression and steps how to achieve TOP5 Customers.

Re: TOP5 Customers based on sales

pradosh_thakur — Fri, 20 Oct 2017 15:09:48 GMT

please find the attached

regards

Pradosh

Re: TOP5 Customers based on sales

pradosh_thakur — Fri, 20 Oct 2017 15:12:43 GMT

1: Method is already described by MARK

2: IN THE SORT tab use rank(sum(sales))> 0 in customer sorting

regards

Pradosh

Re: TOP5 Customers based on sales

passionate — Fri, 20 Oct 2017 15:13:45 GMT

Use calculated dimension as =Aggr(if(rank(sum(Sales))<=5,Customers),Customers)

Expresion as Sum(Sales)

Re: TOP5 Customers based on sales

passionate — Fri, 20 Oct 2017 15:14:31 GMT

Also check suppress null option

Re: TOP5 Customers based on sales

Anonymous — Sat, 21 Oct 2017 04:10:38 GMT

Got it. Thank u so much friends.

Re: TOP5 Customers based on sales

manoj217 — Sat, 21 Oct 2017 05:50:54 GMT

=concat ( IF( aggr ( Rank(sum(Sale)),Customer)<=5, Customer & chr(10)))

Re: TOP5 Customers based on sales

pradosh_thakur — Sat, 21 Oct 2017 06:40:15 GMT

please close the thread by marking someone's answer correct and the answer which helped you as helpful. you can mark more than one answer as helpful.

regards

Pradosh