2 Replies Latest reply on Jan 23, 2012 2:43 PM by Dusty G.

    Means, Medians, and Modes...oh my

    Dusty G.

      I have a dataset that has three dimensions (2 random id's and a 'bin') and a measure of a count.  This is basically a distribution that I can aggregate across the 2 random ID's.  I would like to make calculated fields that would let me show the mean, median, and mode easily, but I'm not entirely sure how to do that.  The mean is easy, the mode and median are not so obvious.  Here is how I would do the mean

       

      MEAN = TOTAL(SUM(x*y))/TOTAL(SUM(y))

       

      I have been able to get kind of a proxy for the median visually by making an indicator like this iif(RUNNING_SUM(SUM(y))/TOTAL(SUM(y))<.5,1,0), but I have not been able to make a single field that will give me the median for the set.  The mode should also be easy since it is really just the x where the y is the MAX...but I'm not sure how to do this either.

       

      I have attached the dataset and a packaged workbook with examples of what I would like to do.

       

      I appreciate your help!

       

      Thanks,

      Dusty

        • 1. Re: Means, Medians, and Modes...oh my
          Richard Leeke

          I wrote up a general approach to calculating quantiles here.  Median is of course an example of a quantile.  Given how your data is structured I had to tweak that slightly.  BTW, I make it that the median is 5, not 3 (I haven't actually checked the data, but I'm fairly confident in that calculation).

           

          For calculating the mode, you can use this table calculation:

           

          WINDOW_MAX(IIF(SUM(y)==WINDOW_MAX(SUM(y)),ATTR(x),0))

           

          BTW - I see you're still on 6.0 (so I did the attached in 6.0), but it's well worth upgrading to 7.0.

           

           

          Edit: I should have pointed out that although I called the calculated field "Median", it is of course a general quantile, so you can just change the parameter p from 0.5 to 0.95 to get the 95th percentile (or whatever).

          • 2. Re: Means, Medians, and Modes...oh my
            Dusty G.

            Wow - amazing.  Thank you very much Richard!  We are in process of upgrading our server to 7 which is why I haven't upgraded my desktop yet.  We're definitely on board though!

             

            Thanks,

            Dusty