topic Avg of top 3 in QlikView

Avg of top 3

Wed, 13 Jul 2011 05:07:18 GMT

Hi,

I have a pivot table like this:

Test	Best (of 10 runs)	Average (of top 3 out of 10 runs)
1
2

My data is organized in the DB table like this:

I am able to calculate the best of 10 runs, using this expression - if(Match(Test,'1'),Max(Value))

For average - this isnt cutting it - if(Match(Test,'1') and (Rank(Value)<=3),Avg(Value))

Any ideas?

Thanks,

Pavithra

Miguel_Angel_Baeyens — Wed, 13 Jul 2011 07:15:34 GMT

Hello Pavithra,

My guess without knowing your data model and expected results would be

Avg(If(Aggr(Rank(Sum(Value), 4, 1), Test) <= 3, Value)

Hope that helps.

BI Consultant

Wed, 13 Jul 2011 19:40:31 GMT

Sorry Miguel, this didnt work.

I am trying to display the average of the top 3 iterations, for each test in my pivot table column. Not sure why you used Rank(Sum(Value).

Anyway, I tried, your expression and this as well -

Avg(If(Aggr(Rank(Value), 4, 1), Test) <= 3, Value)

and its not working nor is this -

Avg(If (Rank(Value, 4, 1) <= 3, Value)