topic Trimming Outliers Numeric data based on value (between 5%ile and 95%ile) in QlikView

Trimming Outliers Numeric data based on value (between 5%ile and 95%ile)

Fri, 26 Jun 2015 14:37:23 GMT

I'm trying to do an "Average If" to basically trim data that is too high or too low for the general smell test.

Here is my expression:

AVG(

IF(

FRACTILE([BalSht_TotalAssets_Total_assets],.05) < [BalSht_TotalAssets_Total_assets] AND [BalSht_TotalAssets_Total_assets] < FRACTILE([BalSht_TotalAssets_Total_assets],.95),

[BalSht_TotalAssets_Total_assets],

NULL())

)

This does not seem to work. I was curious if there was a better way, or if I'm doing something incorrectly.

I'd like to eventually tie this out to 2 variables. So the user can use a slider to set the fences, but defaulted to the .5 and .95.

I'd like to use it to do not only avg, but also Fractiles for 25th, 50th, and 75th

Re: Trimming Outliers Numeric data based on value (between 5%ile and 95%ile)

swuehl — Fri, 26 Jun 2015 15:09:47 GMT

Try

AVG(

IF(

FRACTILE(TOTAL [BalSht_TotalAssets_Total_assets],.05) < [BalSht_TotalAssets_Total_assets] AND [BalSht_TotalAssets_Total_assets] < FRACTILE(TOTAL [BalSht_TotalAssets_Total_assets],.95),

[BalSht_TotalAssets_Total_assets],

NULL())

)

Re: Trimming Outliers Numeric data based on value (between 5%ile and 95%ile)

Fri, 26 Jun 2015 15:17:47 GMT

Not sure which is better, but this seemed to work for me

avg(if([BalSht_CurrentAssets_Cash_on_hand_in_banks] > aggr(NODISTINCT fractile([BalSht_CurrentAssets_Cash_on_hand_in_banks], vLTrim),[fy_end]) and [BalSht_CurrentAssets_Cash_on_hand_in_banks] < aggr(NODISTINCT fractile([BalSht_CurrentAssets_Cash_on_hand_in_banks] , vUTrim),[fy_end]),[BalSht_CurrentAssets_Cash_on_hand_in_banks] )),