Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Aggr Function - Help Needed

Capture.JPG

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

Labels (1)
2 Solutions

Accepted Solutions
Highlighted
Partner
Partner

Re: Aggr Function - Help Needed

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

View solution in original post

Highlighted
Creator II
Creator II

Re: Aggr Function - Help Needed

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

View solution in original post

4 Replies
Highlighted
Partner
Partner

Re: Aggr Function - Help Needed

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

View solution in original post

Highlighted
Creator II
Creator II

Re: Aggr Function - Help Needed

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

Highlighted
Partner
Partner

Re: Aggr Function - Help Needed

TeamRscCntFactorRscCnt*Factor
US70.453.15
UK80.453.6
HK90.655.85

 

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

Highlighted
Creator II
Creator II

Re: Aggr Function - Help Needed

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

View solution in original post