topic Re: Average Excluding Outliers in QlikView

Average Excluding Outliers

Mon, 23 Dec 2013 20:53:44 GMT

I have a very basic function calculating the average.

Avg ([Dictation Time])

The issue is I want to throw out the top 5% highest values.
For instance if I had :

106

899

999

We would discard 899 and 999 as outliers and calculate the average
of the remaining items. Any thoughts on how to do this?

Nicole-Smith — Mon, 23 Dec 2013 21:04:23 GMT

avg({<[Dictation Time]={'<$(=max([Dictation Time], $(=count({1}Data)*.05)))'}>}[Dictation Time])

Tue, 24 Dec 2013 16:31:37 GMT

Thanks, I just this but it is only removing teh TOP value rather than the TOP 5%.

Gysbert_Wassenaar — Tue, 24 Dec 2013 18:30:01 GMT

There are 20 values, so the top 5% values is only 999, not 899 and 999. Those would be the top 10% values.

You can create a variable, e.g. vCutOff, as =max([Dictation Time],- ceil(count([Dictation Time])*0.05))

Then the expression to calculate the average is =avg({<[Dictation Time]={'<$(vCutOff)'}>}[Dictation Time]).