topic Truncated Average, or "trimmed mean" in QlikView

Truncated Average, or "trimmed mean"

dukane24 — Wed, 12 Feb 2014 17:49:37 GMT

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"

rwunderlich — Wed, 12 Feb 2014 18:03:15 GMT

See

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

That example presents various options for excluding outliers.

-Rob

http://masterssummit.com

Re: Truncated Average, or "trimmed mean"

Thu, 20 Feb 2014 13:36:11 GMT

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