topic Re: Remove outliers from Average / Top bottom N in QlikView

Remove outliers from Average / Top bottom N

juleshartley — Tue, 24 Jul 2018 13:39:09 GMT

Hi Qlikers!

i have data similar to this:

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:

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

Thanks

sunny_talwar — Tue, 24 Jul 2018 13:44:40 GMT

Try this

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

juleshartley — Tue, 24 Jul 2018 14:06:59 GMT

Sunny, you're a star!