Remove outliers from Average / Top bottom N

[juleshartley]

Hi Qlikers!

i have data similar to this:

Key	Date	Value
1	01/01/2001	5
1	02/01/2001	5	x
1	03/01/2001	0	x
1	04/01/2001	10
1	05/01/2001	10	x
1	06/01/2001	15	x
2	01/01/2001	20
2	02/01/2001	20
2	03/01/2001	0	x
2	04/01/2001	15	x
2	05/01/2001	20	x
2	06/01/2001	25	x

And I'd like to get an avg per day, by Key but excluding the bottom 2 (and top 2) from the average- ie. where the x's are marked above.

The results would, therefore, be:

Key	Avg
1	7.5
2	20

I've considered, Rank, FirstSortedValue etc. but not sure how to implement. Any ideas!

Thanks

[sunny_talwar · Accepted solution]

Try this

=Avg(Aggr(If(Num(Rank(Value, 4, 0)) > 2 and Num(Rank(-Value, 4, 0)) > 2, Value), Key, Date))

[juleshartley]

Sunny, you're a star!