Help with AGGR Statement

[jcampbell474]

I need to ask for some help with an expression.  I’m not fluent in the ways of AGGR, but can usually figure it out.  Except this time…

Here is the scenario:  Call center reps can be staffed in up to 8-10 workgroups (skills). They have an amount of time available in each workgroup that must be summed, then equally distributed among all of their workgroups.  In other words, a rep has ten (10) workgroups and an hour of available time in each.  Ten (10) total hours of available time.  The total time must be divided by the count of workgroups.  This example is relatively simple as the number of workgroups each rep is assigned to varies and their available time almost always varies.  This example is easy to execute in a straight table with the reps UserId as a dimension.  I’m using: sum(AvailMins)/count(DISTINCT Total <UserId> {<cReportGroup=>}(cReportGroup))

The challenge is getting the same number(s) when the UserId dimension is removed. We need an overall summary of available time by workgroup, without the user detail. I’ve been reading up on AGGR and trying different approaches, but haven’t had any luck.  Here is the last expression I tried: aggr(sum(AvailMins)/count(DISTINCT Total <UserId,cReportGroup> cReportGroup), cReportGroup)

Here are a few others:

sum(aggr(sum(AvailMins)/count(DISTINCT Total <UserId,cReportGroup> cReportGroup), cReportGroup,UserId))

If(Sum(WorkGrpMins) > 0, sum(total <UserId,FiscalYearMonth,cReportGroup> {<cReportGroup=,Division=, Grouping=, CS_Dept=>} AvailMins))

(AvailMins/count(DISTINCT Total(cReportGroup)))/count(DISTINCT Total{<cReportGroup=>}(cReportGroup))

Here is a screenshot.  Everything matches except for the AvailableMins.  The lower table has UserId, the upper table doesn’t. That’s the only difference.

Can anyone help me with the workgroup summary AGGR statement?

Any help would be greatly appreciated!

Thanks in advance,

JC

[stabben23]

Try this one:

sum(AvailMins)/count(Total <UserId> aggr(Count({<cReportGroup=>}cReportGroup),cReportGroup))

[jcampbell474]

Thank you, but it didn't work.  It gives the same value as above: 171,155.

[rubenmarin]

Hi Jason, try setting the same dimensions the chart uses as Aggr() parameters:

aggr(sum(AvailMins)/count(DISTINCT Total <UserId> {<cReportGroup=>}(cReportGroup)), Division, cReportGroup)

The italic text is your current expression (if it's not, change to your current expression in the chart with dimensions)

In bold what I added.

[stabben23]

Ok, just check to use sum instead of Count here:

sum(AvailMins)/sum(Total <UserId> aggr(Count({<cReportGroup=>}cReportGroup),cReportGroup))

[rubenmarin · Accepted solution]

So in the screenshot the upper table is the incorrect one? In that case maybe:

sum(aggr(sum(AvailMins)/count(DISTINCT Total <UserId> {<cReportGroup=>}(cReportGroup)), UserId))

Note: I forgot the external sum() in the last answer.

[sunny_talwar]

Would you be able to share a sample to show the issue so that we can play around with it?

[jcampbell474]

Thank you, but that didn't work either.  It brought the number down to 72.

Ruben's last reply appears to be working, for now.  Data is being validated right now.

Again, thank you.

[jcampbell474]

Ruben, the last expression appears to be working.  We are validating the data right now.  I'll mark your answer as the correct one when we're done.

I really appreciate your help!

[jcampbell474]

Sunny, this is another one of those apps with sensitive data.  I wish I could.  Also, I think Ruben's reply might do the trick.

Thank you!