topic Unique Weekly Average in QlikView

Unique Weekly Average

Tue, 22 Nov 2011 21:09:18 GMT

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

swuehl — Tue, 22 Nov 2011 22:34:28 GMT

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

Unique Weekly Average

Wed, 23 Nov 2011 14:55:09 GMT

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!

Unique Weekly Average

swuehl — Wed, 23 Nov 2011 15:02:09 GMT

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).

Unique Weekly Average

Wed, 23 Nov 2011 15:08:09 GMT

Thanks that worked perfectly! I greatly appreciate your help.