4 Replies Latest reply: Feb 6, 2018 2:40 PM by Mark Gertgen

# Aggregate with new dimension - help with grain

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.

TitleAverage Activities per Day
Job 1100
Job 2

200

Job 350
Job 410

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.

TitleEmployeeActivities TodayAverage Activities per Day
Job 1John78100
Job 1Mary112100
Job 2Mark224200
Job 3Sally4750
Job 4Sara1310

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

• ###### Re: Aggregate with new dimension - help with grain

Try something like

Avg(TOTAL<[JobTitle]> Aggr(Sum(TOTAL<Date,[Job Title]> [Activity Counter]), [Date], [Job Title], [Employee]))

• ###### Re: Aggregate with new dimension - help with grain

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

• ###### Re: Aggregate with new dimension - help with grain

It's probably better to calculate your average by dividing an aggregate by a count of your grouping entities.

See

Average – Which average?

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?

• ###### Re: Aggregate with new dimension - help with grain

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