Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
conor_gallagher93
Contributor III
Contributor III

Summing Aggregated Results to find Adjusted Productivity Numbers

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:

factoractivity_dateusercompletedexp_completedhrsmaxprod
0.855/11/202002.F85Z.230288106.045158
0.855/12/202002.F85Z.230288106.045158
0.855/13/202002.F85Z.230288416.045158
0.855/14/202002.F85Z.230288306.045158
0.855/15/202002.F85Z.230288406.045158
0.855/16/202002.F85Z.230288NULLNULL0158
0.855/17/202002.F85Z.230288NULLNULL0158
0.855/18/202002.F85Z.230288NULLNULL6.045158
0.855/19/202002.F85Z.230288206.045158
0.855/20/202002.F85Z.230288306.045158
0.855/21/202002.F85Z.230288206.045158
0.855/22/202002.F85Z.230288314.295158
0.855/23/202002.F85Z.230288NULLNULL0158
0.855/24/202002.F85Z.230288NULLNULL0158
0.855/25/202002.F85Z.230288NULLNULL0158
0.855/26/202002.F85Z.230288NULLNULL0158
0.855/27/202002.F85Z.230288200158
0.855/28/202002.F85Z.230288730158
0.855/29/202002.F85Z.230288200158
0.855/30/202002.F85Z.230288NULLNULL0158
0.855/31/202002.F85Z.230288NULLNULL0158
0.655/1/202002.F85Z.230288NULLNULL6.045158
0.655/2/202002.F85Z.230288NULLNULL0158
0.655/3/202002.F85Z.230288NULLNULL0158
0.655/4/202002.F85Z.230288116.045158
0.655/5/202002.F85Z.230288206.045158
0.655/6/202002.F85Z.230288206.045158
0.655/7/202002.F85Z.230288306.045158
0.655/8/202002.F85Z.230288NULLNULL6.045158
0.655/9/202002.F85Z.230288NULLNULL0158
0.655/10/202002.F85Z.230288NULLNULL0158
0.655/31/202002.F85Z.230288NULLNULLNULL158

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({<[factor]={">-1"}>} [TotComp])
/
Sum({<[factor]={">-1"}>} [hrs]) * 155) > [maxprod],
Sum({<[factor]={">-1"}>} [TotComp])
,
	IF(
	(Sum({<[factor]={">-1"}>} [TotComp])/Sum({<[factor]={">-1"}>} [hrs])*155) < [maxprod]
    and 
    SUM(AGGR(((Sum({<[factor]={">-1"}>} [TotComp])/Sum({<[factor]={">-1"}>} [hrs]) * 155)/[factor]), [factor]
    )) > [maxprod],
    
	[maxprod]*SUM([hrs])/155,
    
    SUM(
    AGGR(Sum({<[factor]={">-1"}>} [TotComp])/[factor], [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!

Labels (5)
0 Replies