topic Avarage from Top 5 in QlikView

Avarage from Top 5

Thu, 10 Jun 2010 14:27:08 GMT

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!

Avarage from Top 5

Thu, 10 Jun 2010 14:45:32 GMT

Hi Eugen,

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

AW:Avarage from Top 5

Fri, 11 Jun 2010 10:04:01 GMT

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

Avarage from Top 5

johnw — Fri, 11 Jun 2010 23:56:26 GMT

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.

AW:Avarage from Top 5

Mon, 14 Jun 2010 07:29:46 GMT

For all who are interested, here is the solution:

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

Until next time