Aggr Function - Help Needed

tschullo — Sat, 16 Nov 2024 03:48:33 GMT

I am trying to display the planned hours by quarter against available hours where the available hours are based on (40 hour week x 52 weeks)/4 = 520. We then take that number and lookup the resource count by Team and multiply the 520 by that. Additionally, since team members work on things other than audits, there is a factor assigned by team that is used as a final multiplier as shown in the above data chart.

The issue is that in the bar graph, I need to calculate the time available by aggregating the product of all these factors based on team and any filters that might be set limiting which team we are looking at,

The expression I wrote which is not working is: =Aggr(Sum(Sum(QtrWorkHours)*RscCnt*Factor),Team) as well as =Aggr(Sum(QtrWorkHours*RscCnt*Factor),QtrWorkHours,Team).

The Planned Hours bar chart expression is just: Sum(PlannedHrs)

Any ideas?

Here is the script for some sample data:

AuditData:
load * inline [
AuditID, AuditDesc, Qtr, PlannedHrs
101, Audit 101, 2019Q2, 50
102, Audit 102, 2019Q2, 70
103, Audit 103, 2019Q4, 500
104, Audit 104, 2019Q2, 250
105, Audit 105, 2019Q3, 150
106, Audit 106, 2019Q2, 520
107, Audit 107, 2019Q2, 450
108, Audit 108, 2019Q3, 150
109, Audit 109, 2019Q4, 250
110, Audit 110, 2019Q3, 150
111, Audit 111, 2019Q4, 550
] ;

AnalyticsTeam:
load * inline [
AuditID, Team
101, UK
102, US
102, UK
103, HK
104, HK
105, US
105, UK
105, HK
106, UK
107, HK
108, HK
109, UK
110, HK
111, HK
] ;

QtrHrs:
load * inline [
Qtr, QtrWorkHours
2019Q1, 520
2019Q2, 520
2019Q3, 520
2019Q4, 520
] ;

TeamMakeup:
load * inline [
Team, RscCnt, Factor
US,  7,  .45
UK,  8,  .45
HK,  9,  .65
] ;

Re: Aggr Function - Help Needed

felipe_dutra — Fri, 19 Apr 2019 03:40:12 GMT

Sum(Aggr(Sum(RscCnt*Factor)*Avg(QtrWorkHours), Qtr))

Re: Aggr Function - Help Needed

tschullo — Fri, 19 Apr 2019 12:39:03 GMT

That seems to work... How is it accounting for the fact that the factor varies by team?

Re: Aggr Function - Help Needed

felipe_dutra — Sat, 20 Apr 2019 01:17:48 GMT

Team	RscCnt	Factor	*RscCntFactor**
US	7	0.45	3.15
UK	8	0.45	3.6
HK	9	0.65	5.85

Sum(RscCnt*Factor) = 3.15 + 3.6 + 5.85 = 12.6

Re: Aggr Function - Help Needed

tschullo — Mon, 22 Apr 2019 13:51:32 GMT

When I added it to my dashboard, I found that I did have to specify Team as another group by field:

Sum(Aggr(Sum(RscCnt*Factor)*Avg(QtrWorkHours), Qtr, Team))

topic Re: Aggr Function - Help Needed in QlikView

Aggr Function - Help Needed

Re: Aggr Function - Help Needed

Re: Aggr Function - Help Needed

Re: Aggr Function - Help Needed

Re: Aggr Function - Help Needed