topic Re: Help with AGGR Statement in QlikView

Help with AGGR Statement

jcampbell474 — Wed, 23 Nov 2016 13:31:27 GMT

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,

Re: Help with AGGR Statement

stabben23 — Wed, 23 Nov 2016 13:37:48 GMT

Try this one:

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

Re: Help with AGGR Statement

jcampbell474 — Wed, 23 Nov 2016 13:41:09 GMT

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

Re: Help with AGGR Statement

rubenmarin — Wed, 23 Nov 2016 13:43:56 GMT

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.

Re: Help with AGGR Statement

stabben23 — Wed, 23 Nov 2016 13:44:23 GMT

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

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

Re: Help with AGGR Statement

rubenmarin — Wed, 23 Nov 2016 13:46:34 GMT

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.

Re: Help with AGGR Statement

sunny_talwar — Wed, 23 Nov 2016 13:48:29 GMT

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

Re: Help with AGGR Statement

jcampbell474 — Wed, 23 Nov 2016 13:55:06 GMT

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.

Re: Help with AGGR Statement

jcampbell474 — Wed, 23 Nov 2016 13:56:53 GMT

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!

Re: Help with AGGR Statement

jcampbell474 — Wed, 23 Nov 2016 13:58:07 GMT

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!

Re: Help with AGGR Statement

sunny_talwar — Wed, 23 Nov 2016 14:00:16 GMT

As long as you get a working expression, you don't have to share anything . In case you have issues, please provide some more details so that we can give a more educated response.

Best,

Sunny