Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi
Try like below
Sum(Aggr(If(rank(Sum(SalesAmount),4)<=3, Sum(SalesAmount),0), Customer, Day))
Hi
Try like below
Sum(Aggr(If(rank(Sum(SalesAmount),4)<=3, Sum(SalesAmount),0), Customer, Day))
Thanks a lot Mayil, that´s exactly what I was looking for.