0 Replies Latest reply: Feb 3, 2016 6:58 AM by Danielle van Eeden RSS

    Aggr - Count Records Greater than Average

    Danielle van Eeden

      Hi All,

       

      I'm doing some analysis around supplier benchmarking and I have a chart by supplier which has the following expressions;

       

      * number of records for that supplier

                count({$<Type = {"ABC"}>} distinct(Records))

       

      * avg number of sessions taken to achieve completion

                avg({$<Type = {"ABC"}>} SessionNumber)

       

      * avg number of sessions taken to achieve completion, across all suppliers

                avg({$<Type = {"ABC"}, Supplier = >} total <Records> SessionNumber)

       

      I now need to add in an expression showing the count of Records where the average number of sessions taken to achieve completion is greater than the overall average (across all suppliers). So in the example above, I would need to count all records where the sessions taken to achieve completion is greater then 1.8.

       

      I've tried various things with AGGR but don't seem to be getting anywhere - my latest attempt is just returning 0, and now I've got myself totally lost and not  clue if I'm even on the right track!

       

      count( aggr( if( (avg({$<Type = {"ABC"}>}SessionNumber))>(avg({$<Type = {"ABC"},Supplier = >}total <Records> SessionNumber)),

      (count({$<Type = {"ABC"}>}distinct (Records)))),  Supplier, Records))

       

      Any help greatly appreciated!