2 Replies Latest reply: Jun 26, 2015 11:17 AM by Matt Mohon RSS

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

    Matt Mohon

      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