4 Replies Latest reply: Nov 23, 2011 10:08 AM by Robyn Talley RSS

    Unique Weekly Average

      I need a weekly average givng me a count of people doing presentations.  My current expression counts each person unique and only sums distinct people over the filtered time period.  How do I write my expression to count people unique each week then give me an average of those individual week counts??

       

      The Thursday field is my week ending date.

       

      Below is my current expression:

       

      count({DidAPresentation = {'Yes'}>} distinct EmployeeName)/count(distinct Thursday)

       

      Thanks for the help!

        • Unique Weekly Average
          Stefan Wühl

          talleyrobyn,

           

          I think you could use advanced aggregation, aggr() function, to achieve what you want.

           

          I assume that you have a week ending date associated to each presentation record. If not, I would suggest that you create e.g. a continuous week number field from the presentation date that you probably have.

           

          The expression could look similar to

           

          = sum( aggr( count({<DidAPresentation = {'Yes'} >} distinct EmployeeName), Thursday)) / count (distinct Thursday)

           

          Hope this helps,

          Stefan