Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey all, I have an app that has 30 days of employee activity. In it I have a table that shows job titles and the average number of activities per day for each job title.
Title | Average Activities per Day |
---|---|
Job 1 | 100 |
Job 2 | 200 |
Job 3 | 50 |
Job 4 | 10 |
Avg(Aggr(Sum([Activity Counter]), [Date], [Job Title]))
What I want to do now is add an employee dimension and show their activity for the current day and still show the average activity for their job title.
Title | Employee | Activities Today | Average Activities per Day |
---|---|---|---|
Job 1 | John | 78 | 100 |
Job 1 | Mary | 112 | 100 |
Job 2 | Mark | 224 | 200 |
Job 3 | Sally | 47 | 50 |
Job 4 | Sara | 13 | 10 |
I can add the employee and Sum([Activity Counter]), but changing the grain of the table breaks the Average Activities per Day calculation. I can't seem to find an example of how to do this. I tried to have it ignore the Employee dimension but it doesn't work.
Avg(Aggr(Sum([Activity Counter]), [Date], [Job Title] <[Employee]>))
I know my aggregation has to be at the same grain (or finer) of the chart but I need to keep the average per day for each job title.
Any help would be appreciated!
Thanks,
~Mark
It's probably better to calculate your average by dividing an aggregate by a count of your grouping entities.
See
I assume that the employee dimension corrupts your average when using the Avg() other aggregation function.
How does your raw data looks like? Could you post a small sample QVW together with your expected result?
Try something like
Avg(TOTAL<[JobTitle]> Aggr(Sum(TOTAL<Date,[Job Title]> [Activity Counter]), [Date], [Job Title], [Employee]))
Hi Stefan! Thanks for taking the time to help me out. This is close but most of the average activities per day numbers aren't correct. (Some are, however.) The ones that are off are close to the correct values but not spot on. Strange.
I'll keep at it with this is a starting point but if you have any other ideas I'd sure appreciate it.
Thanks again,
~Mark
It's probably better to calculate your average by dividing an aggregate by a count of your grouping entities.
See
I assume that the employee dimension corrupts your average when using the Avg() other aggregation function.
How does your raw data looks like? Could you post a small sample QVW together with your expected result?
Thank you Stefan! Your link to HICs post put me on the path to success. Here is what ended up working:
Sum(Total <[Job Title]> [Activity Counter] ) / Count(Distinct Total <[Job Title]> [Date] & '|' & [Job Title])
This was a good learning exercise for me and I appreciate the 'nudge' towards the right solution. (Seems so obvious in retrospect.)
Thanks again,
~Mark