Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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))
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))
You can use as calculated dimension:
=aggr(Only({<Sales={'>$(=Max(Sales,5))'}>} Customer),Customer)
or with Customer as dimension
Sum({<Sales={'>$(=Max(Sales,5))'}>} Sales)
As expression, even better
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?
add this as a calculated dimension
=Aggr(If(Rank(avg(Sales)) <= 4, Customer), Customer)
Why AVG here?
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 ?
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)
I need top 4 not top 5...