TRIM MEAN Not Functioning as expected.....

[bmchale1983]

I have been trying to create a trim mean calculation to exclude top 5 and bottom 5 % based on duration....

The base calculation without the trim is:  Avg({<[Metric Type]={'Completed'},[Metric Date]={$(v13Month)}>}[Duration Completion])

I have been using the following for trim mean, but not functioning:

avg({<[Metric Type]={'Completed'},[Metric Date]={$(v13Month)}>}
if(
[Duration Completion] > aggr(NODISTINCT fractile([Duration Completion], 0.05), [Metric Type],[IR Metric Date])
and
[Duration Completion] < aggr(NODISTINCT fractile([Duration Completion], 0.95), [Metric Type],[IR Metric Date]),[Duration Completion]
)
)

Also appears count is not accurate, because for my counts to original is only approximately 600 and withing trim mean aggregation its approx 4k...  

[lfetensini]

Use Rank() to do "Top" and Aggr() by your dimension.

See more details in:

https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/RankingFu...

[bmchale1983]

I used Rank as mentioned and am very close, however I need to only consider rank when Field equals Completed and will need to add a time dimension of 13 months which is in a variable...

CALCULATION THAT WORKS WHEN I SET FILTER MANUALLY:

if(

Rank( total [Duration Completion - IR],4,1)<(count({<[IR Metric Type]={'Completed'}>}total <[IR MonthYear]> [Duration Completion - IR])*.95)

and

Rank( total [Duration Completion - IR],4,1)>count({<[IR Metric Type]={'Completed'}>}total <[IR MonthYear]> [Duration Completion - IR])*.05

,avg([Duration Completion - IR]))

I need to replace BOLD UNDERLINE with something like this, but can’t seem to get it to work….  I am going to continue trouble shooting, but if you can recommend a quick statement would greatly appreciate it.

Rank(aggr({< [IR Metric Type]={'Complete'} >} [Duration Completion - IR]),4,1)

[rwunderlich]

I haven't looked at your expression in detail, but here is a similar example that works:

Qlikview Cookbook: Outliers http://qlikviewcookbook.com/recipes/download-info/outliers/

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com