Summing Aggregated Results to find Adjusted Productivity Numbers

[conor_gallagher93]

I'm trying to build a chart calculation that will report on my company's adjusted productivity results. Here's a sample of what the source data look like:

factor	activity_date	user	completed	exp_completed	hrs	maxprod
0.85	5/11/2020	02.F85Z.230288	1	0	6.045	158
0.85	5/12/2020	02.F85Z.230288	1	0	6.045	158
0.85	5/13/2020	02.F85Z.230288	4	1	6.045	158
0.85	5/14/2020	02.F85Z.230288	3	0	6.045	158
0.85	5/15/2020	02.F85Z.230288	4	0	6.045	158
0.85	5/16/2020	02.F85Z.230288	NULL	NULL	0	158
0.85	5/17/2020	02.F85Z.230288	NULL	NULL	0	158
0.85	5/18/2020	02.F85Z.230288	NULL	NULL	6.045	158
0.85	5/19/2020	02.F85Z.230288	2	0	6.045	158
0.85	5/20/2020	02.F85Z.230288	3	0	6.045	158
0.85	5/21/2020	02.F85Z.230288	2	0	6.045	158
0.85	5/22/2020	02.F85Z.230288	3	1	4.295	158
0.85	5/23/2020	02.F85Z.230288	NULL	NULL	0	158
0.85	5/24/2020	02.F85Z.230288	NULL	NULL	0	158
0.85	5/25/2020	02.F85Z.230288	NULL	NULL	0	158
0.85	5/26/2020	02.F85Z.230288	NULL	NULL	0	158
0.85	5/27/2020	02.F85Z.230288	2	0	0	158
0.85	5/28/2020	02.F85Z.230288	7	3	0	158
0.85	5/29/2020	02.F85Z.230288	2	0	0	158
0.85	5/30/2020	02.F85Z.230288	NULL	NULL	0	158
0.85	5/31/2020	02.F85Z.230288	NULL	NULL	0	158
0.65	5/1/2020	02.F85Z.230288	NULL	NULL	6.045	158
0.65	5/2/2020	02.F85Z.230288	NULL	NULL	0	158
0.65	5/3/2020	02.F85Z.230288	NULL	NULL	0	158
0.65	5/4/2020	02.F85Z.230288	1	1	6.045	158
0.65	5/5/2020	02.F85Z.230288	2	0	6.045	158
0.65	5/6/2020	02.F85Z.230288	2	0	6.045	158
0.65	5/7/2020	02.F85Z.230288	3	0	6.045	158
0.65	5/8/2020	02.F85Z.230288	NULL	NULL	6.045	158
0.65	5/9/2020	02.F85Z.230288	NULL	NULL	0	158
0.65	5/10/2020	02.F85Z.230288	NULL	NULL	0	158
0.65	5/31/2020	02.F85Z.230288	NULL	NULL	NULL	158

The date and user fields are self-explanatory, but the rest are used to calculate productivity. First we add the completed and exp_completed fields together to get TotComp. Then we calculate PROD as: Sum(TotComp)/Sum(hrs)*155.

The trouble I'm having relates to the factor column. For newer users, we calculate an adjusted TotComp number where we divide Sum(TotComp) by the factor, so long as doing so does not put the user above the MaxProd number. The factor-wise results are then added to get the adjusted TotComp which is then plugged into the PROD calculation.

I have tried to build a chart script based on the following conditions to get the adjusted completed sums:

If(PROD > MaxProd, Sum(completed + exp_completed), IF(PROD < MaxProd and PROD/factor > MaxProd, MaxProd*hrs/155, Sum(completed + exp_completed)/factor))

Which I thought worked out to the following code:

IF(
(Sum({<[tcr1_coremetrics.factor]={">-1"}>} [TCR1_Data.TotComp])
/
Sum({<[tcr1_coremetrics.factor]={">-1"}>} [TCR1_Data.hrs]) * 155) > [maxprod],
Sum({<[tcr1_coremetrics.factor]={">-1"}>} [TCR1_Data.TotComp])
,
	IF(
	(Sum({<[tcr1_coremetrics.factor]={">-1"}>} [TCR1_Data.TotComp])/Sum({<[tcr1_coremetrics.factor]={">-1"}>} [TCR1_Data.hrs])*155) < [maxprod]
    and 
    SUM(AGGR(((Sum({<[tcr1_coremetrics.factor]={">-1"}>} [TCR1_Data.TotComp])/Sum({<[tcr1_coremetrics.factor]={">-1"}>} [TCR1_Data.hrs]) * 155)/[tcr1_coremetrics.factor]), [tcr1_coremetrics.factor]
    )) > [maxprod],
    
	[maxprod]*SUM([TCR1_Data.hrs])/155,
    
    SUM(
    AGGR(Sum({<[tcr1_coremetrics.factor]={">-1"}>} [TCR1_Data.TotComp])/[tcr1_coremetrics.factor], [tcr1_coremetrics.factor])
    ))
)

However, for the above sample data, Qlik is returning a value of 96.81 instead of the correct value, 59.73. I believe this issue relates to my AGGR functions not totaling the amounts correctly, but I'm not sure what in my code is causing the issue.

I'd appreciate anyone's insights on what should be modified to get the chart function working correctly. And please let me know if I should explain anything else to help find a solution!