2 Replies Latest reply: Jul 13, 2011 3:40 PM by pmahamani

# Avg of top 3

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:

TestRun
Value

1

1

100

12200
......
110300
2110

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

• ###### Re: Avg of top 3

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.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

• ###### Avg of top 3

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)