Avarage from Top 5

[#1]

Hi all,

how can i calculate the avarage from Top 4 positions.

F1	Rank
1	1
5	2
12	3
14	4
51	5
41	6

(1+5+12+14)/4

I trying this:

if(aggr(rank(count(F1)),orderID)<=4,sum(distinct F1)/count(distinct F1))

Thanks!

[#2]

Hi Eugen,

I think you should include the rank also in your calculations. This way it will calculate based on your selection.

[#3]

HI, sorry, i don't now what you mean.

How can I expect with the top 5. I want to calculate the sum of top 5

Thanks

[johnw]

avg(if(Rank<5,F1))

At least for the sample table you posted. It looks like your real data is more complicated, since it mentions an OrderID that isn't in your sample. But you don't show your real data, and first you said you wanted the average of the top four and now you say you want the sum of the top five, so so I can't actually solve the problem, since you haven't given me the real problem to solve.

[#5 · Accepted solution]

For all who are interested, here is the solution:

sum(distinct {<F1={$(=concat(if(aggr(rank( count (F1)),F1)<=5, F1), ', '))}>}

Until next time