Aggr Function - Help Needed

[tschullo]

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
] ;

[felipe_dutra · Accepted solution]

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

[tschullo]

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

[felipe_dutra]

Team	RscCnt	Factor	RscCnt*Factor
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

[tschullo · Accepted solution]

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))