topic Re: Aggregate with new dimension - help with grain in App Development

Aggregate with new dimension - help with grain

magertgen — Mon, 05 Feb 2018 21:46:33 GMT

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

Re: Aggregate with new dimension - help with grain

swuehl — Mon, 05 Feb 2018 21:52:40 GMT

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

magertgen — Mon, 05 Feb 2018 22:56:24 GMT

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

swuehl — Mon, 05 Feb 2018 23:32:24 GMT

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

magertgen — Tue, 06 Feb 2018 19:40:54 GMT

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