Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
dukane24
Contributor III
Contributor III

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.

2 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

See

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

That example presents various options for excluding outliers.

-Rob

http://masterssummit.com

Not applicable

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