2 Replies Latest reply: Feb 20, 2014 8:36 AM by Erica Whalley

# Truncated Average, or "trimmed mean"

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.

• ###### Re: Truncated Average, or "trimmed mean"

See

That example presents various options for excluding outliers.

-Rob

• ###### Re: Truncated Average, or "trimmed mean"

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