Unique Weekly Average

[#1]

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!

[swuehl]

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

[#3]

Thanks Stefan.  The expression worked at the total level.  However, at the dimenison level of employee's it did not work to give me an average per employee.  I

Thanks!

[swuehl · Accepted solution]

Try adding EmployeeName to your aggr dimensions:

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

If your chart dimensions are in order of EmployeeName and Thursday (or if you don't use Thursday as dimension in your chart).

[#5]

Thanks that worked perfectly!  I greatly appreciate your help.