Truncated Average, or "trimmed mean"

[dukane24]

Is there a way to calculate a truncated average or "trimmed mean" in Qlikview? I would like to include this in a straight table or a pivot table.  In Excel there is a function for this called TRIMMEAN.  This allows you to calculate an average that excludes outlying values.  You specify a percentage of high and low values to exclude.  I don't see anything like this listed under the "Aggregate" function category.

[rwunderlich]

See

http://qlikviewcookbook.com/recipes/download-info/outliers/

That example presents various options for excluding outliers.

-Rob

http://masterssummit.com

[#3]

Hi nate,

Without resorting to setting values in the script, you can use the fractile() function to return the top / bottom 5% value and use that to select.

Ideally you should put these values in variables, and use that in set analysis:

=avg({<value={"=>=$(vbottom5Pc)<=$(vtop5Pc)"}>} value)

or if it is a row-by-row operation, use an if statement (but this is a  bit clunky and may be quite slow)

avg

(aggr(if(randnumber>=fractile(randnumber,0.05)
and randnumber<=fractile(randnumber,0.95),randnumber),rownumber))

Or something similar

Regards,

Erica