Show sum of top 3 sales for each customer

[dato6091]

Hi!!

I am having troubles trying to calculate the top 3 sales for each customer. My data is the following:

LOAD * INLINE [
Customer, Day, SalesAmount
1 , 1 , 100
1 , 2 , 103
1 , 3 , 60
1 , 4 , 120
1 , 5 , 89
1 , 6 , 130
2 , 1 , 75
2 , 2 , 75
2 , 3 , 50
2 , 4 , 63
2 , 5 , 89
2 , 6 , 25
3 , 1 , 140
3 , 2 , 200
3 , 3 , 150
3 , 4 , 140
3 , 5 , 35
3 , 6 , 74
];

I am trying to get the sum using the following expression:

Sum({<SalesAmount={">=FirstSortedValue(SalesAmount, -Aggr(Sum(SalesAmount), Customer, Day),3)"}>} SalesAmount)

But it doesn´t work. Any idea of what is wrong with the expression?

Thanks in advance.

[MayilVahanan · Accepted solution]

Hi

Try like below

Sum(Aggr(If(rank(Sum(SalesAmount),4)<=3, Sum(SalesAmount),0), Customer, Day))

[dato6091]

Thanks a lot Mayil, that´s exactly what I was looking for.