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

[#1]

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

[swuehl · Accepted solution]

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())

)

[#3]

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] )),