3 Replies Latest reply on Mar 16, 2018 8:52 AM by alex.apolloni.0

    Stdev of an aggregate in a LOD calculation for use in a KPI and Filter?



      I'd like to do a get the stdev in a calculation so that it can be used as a Filter and to highlight marks like in the below mock up.


      I have an aggregate calculation (in the example Superstore workbook it's [order/custmer] :=  count([Order ID])/countd([Customer Name]).  It's displayed in a chart split by Segment.

      I'd like to have a Filter that said, "only show the charts for the segments where, for the previous month, the [order/customer] value is higher than the 1 standard deviation for [order/customer] for the whole date range."    

      The same calculation would also allow you to display marks for all the months where it was true (Sort of like the below screen shot) and in a KPI.




      The calculation should be something like:

      if ( [orders / customer] > ( [order/cust median (all)] + [order/cust stdev (all)]) )

      then "greater than one stdev" else "less than one stdev"



      My difficulty is that I can't get the either of the median or stdev calculations to work.

      (i thought it'd be something like: STDEV({FIXED :[orders / customer]}) but that comes back as blank)


      Thanks very much for your time.

      Kind Regards,