Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
Use Rank() to do "Top" and Aggr() by your dimension.
See more details in:
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)
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