Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
bmchale1983
Contributor III
Contributor III

TRIM MEAN Not Functioning as expected.....

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...  

Labels (2)
3 Replies
lfetensini
Partner - Creator II
Partner - Creator II

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...

Support your colleagues. Remember to "like" the answers that are helpful to you and flag as "solved" the one that helped you solve. Cheers.
bmchale1983
Contributor III
Contributor III
Author

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