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

[rubenmarin · Accepted solution]

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

[stigchel]

You can use as calculated dimension:

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

[stigchel]

or with Customer as dimension

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

As expression, even better

[MK_QSL]

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?

[ramoncova06]

add this as a calculated dimension

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

[MK_QSL]

Why AVG here?

[richard_chilvers]

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 ?

[stigchel]

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)

[MK_QSL]

I need top 4 not top 5...