Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
See
http://qlikviewcookbook.com/recipes/download-info/outliers/
That example presents various options for excluding outliers.
-Rob
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