Customers Having Top 4 Values

[MK_QSL]

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...

[ramoncova06]

either sum or avg would work

[stigchel]

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

[MK_QSL]

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

  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..

[fvelascog72]

You can try this:

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

and you must order with the same expresion

[MK_QSL]

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

[stigchel]

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)

[ramoncova06]

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

[MK_QSL]

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...

[#10]

hI Manish

try this expression

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

[Gysbert_Wassenaar]

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