10 Replies Latest reply: Nov 23, 2016 9:00 AM by Sunny Talwar RSS

    Help with AGGR Statement

    Jason Campbell

      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.

      OpsSec.PNG

       

      Can anyone help me with the workgroup summary AGGR statement?

      Any help would be greatly appreciated!

       

      Thanks in advance,

      JC