topic Re: Customers Having Top 4 Values in QlikView

Customers Having Top 4 Values

MK_QSL — Thu, 26 Mar 2015 15:25:20 GMT

Consider I have below data..

Load * Inline

[

Customer, Sales

A, 100

B, 120

C, 140

D, 90

E, 45

F, 150

G, 200

H, 120

I, 150

J, 140

];

I want Straight Table Showing Customers having TOP 4 Sales....

TOP 4 Sales is

200, 150, 140, 120

So I need result something like below

Customer SUM(Sales)

G 200

F 150

I 150

C 140

J 140

B 120

H 120

NOTE : I don't need the solution on SCRIPT level...

Re: Customers Having Top 4 Values

rubenmarin — Thu, 26 Mar 2015 15:45:56 GMT

Hi Manish, asked by you I knowed it will be a difficult one, but I'm stubborn and I get something that may help you..

You can use a calculated dimension like:

=Aggr(If(Sum(Sales)>=Max(TOTAL Aggr(Sum(Sales), Customer), 4), Customer), Customer)

Also, if you don't need the total this expression can work avoiding calculated dimension:

If(Sum(Sales)>=Max(TOTAL Aggr(Sum(Sales), Customer), 4), Sum(Sales))

Re: Customers Having Top 4 Values

stigchel — Thu, 26 Mar 2015 16:14:01 GMT

You can use as calculated dimension:

=aggr(Only({<Sales={'>$(=Max(Sales,5))'}>} Customer),Customer)

Re: Customers Having Top 4 Values

stigchel — Thu, 26 Mar 2015 16:15:50 GMT

or with Customer as dimension

Sum({<Sales={'>$(=Max(Sales,5))'}>} Sales)

As expression, even better

Re: Customers Having Top 4 Values

MK_QSL — Thu, 26 Mar 2015 16:16:22 GMT

I have given Sales here but there are always possibility of more than one lines of Sales per customer... So do SUM(Sales) will work here?

Re: Customers Having Top 4 Values

ramoncova06 — Thu, 26 Mar 2015 16:20:26 GMT

add this as a calculated dimension

=Aggr(If(Rank(avg(Sales)) <= 4, Customer), Customer)

Re: Customers Having Top 4 Values

MK_QSL — Thu, 26 Mar 2015 16:23:30 GMT

Why AVG here?

Re: Customers Having Top 4 Values

richard_chilvers — Thu, 26 Mar 2015 16:27:30 GMT

I must be mis-understanding the requirement.

My 1st thought was a straight table with customer as dimension and sum(sales) as expression and then use Dimension Limits to show the top 4 ?

Re: Customers Having Top 4 Values

stigchel — Thu, 26 Mar 2015 16:31:40 GMT

if the requirement is to show sum (all)sales for customers having the top 4 sales then

Sum({<Customer=P({1<Sales={'>$(=Max(Sales,5))'}>} Customer)>} Sales)

Re: Customers Having Top 4 Values

MK_QSL — Thu, 26 Mar 2015 16:35:00 GMT

I need top 4 not top 5...

Re: Customers Having Top 4 Values

ramoncova06 — Thu, 26 Mar 2015 16:38:14 GMT

either sum or avg would work

Re: Customers Having Top 4 Values

stigchel — Thu, 26 Mar 2015 16:40:01 GMT

Then your requirement is not clear to me, according to me this is displaying all the customers having a Top 4 sale value and then summing all their sales

Re: Customers Having Top 4 Values

MK_QSL — Thu, 26 Mar 2015 16:52:08 GMT

Hi Piet,

Please consider below case..

Load *,RowNo() as ID Inline

[

Customer, Sales

A, 100

B, 120

C, 140

D, 90

E, 45

F, 150

G, 100

H, 120

I, 150

J, 140

];

Now accordingly to my requirement, top 4 values are

SUM(Sales)

200, 150, 140, 120

So Need to have below customers in Straight Table and Pivot Table

G 200

F 150

I 150

C 140

J 140

B 120

H 120

But using your expression, it would give me

A 120 also... which is wrong as 120 is not the top 4 value...

Hope this will clear now..

Re: Customers Having Top 4 Values

fvelascog72 — Thu, 26 Mar 2015 16:52:16 GMT

You can try this:

sum({<Sales = {'>=$(=Max(Sales,4))'}>}Sales)

and you must order with the same expresion

Re: Customers Having Top 4 Values

MK_QSL — Thu, 26 Mar 2015 17:09:07 GMT

This will not work for my 2nd example given to Piet

Re: Customers Having Top 4 Values

stigchel — Thu, 26 Mar 2015 18:45:38 GMT

Then the answer from Ruben was correct?

So without the if

Sum({<Customer=P({1<Customer={'=Sum(Sales)>=Max(TOTAL Aggr(Sum(Sales), Customer), 4)'}>} Customer)>} Sales)

Re: Customers Having Top 4 Values

ramoncova06 — Thu, 26 Mar 2015 19:17:37 GMT

Just realized that the avg does give a different value, than by using sum

=Aggr(If(Rank(sum(Sales)) <= 4, Customer), Customer) use this as the calculated dimension and suppress nulls

Re: Customers Having Top 4 Values

MK_QSL — Thu, 26 Mar 2015 19:38:55 GMT

Ruben's answer is correct but waiting for someone if is there any other way to do it.... Otherwise i will close the thread by Ruben's answer tomorrow...

Re: Customers Having Top 4 Values

Fri, 27 Mar 2015 08:19:03 GMT

hI Manish

try this expression

Sum({<Sales={'>$(=Max(Sales,5))'}>} Sales)

Re: Customers Having Top 4 Values

Gysbert_Wassenaar — Fri, 27 Mar 2015 08:42:37 GMT

Try: sum({<Customer={"=sum(Sales)>=$(=max(aggr(sum(Sales),Customer),4))"}>}Sales)