Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Average Excluding Outliers

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 :

12

15

18

19

106

17

54

89

20

87

78

65

98

15

63

32

65

89

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?

3 Replies

Re: Average Excluding Outliers

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

Not applicable

Re: Average Excluding Outliers

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

Re: Average Excluding Outliers

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]).


talk is cheap, supply exceeds demand
Community Browser