topic sum function in script in QlikView

sum function in script

Tue, 22 Dec 2009 21:23:39 GMT

Hello fellow users,

I would like to do a calculation in the script and I don't know how and if it is possible. Here is an excample:

Table 1a:

Contract	Sub-contract	ForcastTotalCost	ForecastActualCost	Result
3000	3000-a	4000	2000	GOOD
3000	3000-b	2500	3000	BAD
3001	3001-a	3000	2000	GOOD
3001	3001-b	200	400	BAD

I would like to calculate the column Result with the following formula:

If(ForcastTotalCost >ForecastActualCost , GOOD, BAD) AS Result.

The Column result gives the following outcome as shown on table 1a above.

The problem now is that the script calculated per row and a row is a Sub-Contract. I would like to do the calculation per Contract, because on a contract total the Colum result should be GOOD and now 3000-a is GOOD and 3000-b is BAD. Does someone know how I can Sum the rows and do the calculation like on table b below??

Contract	Sub-contract	ForcastTotalCost	ForecastActualCost	Result
3000	3000-a	4000	2000	GOOD
3000	3000-b	2500	3000	GOOD
3001	3001-a	3000	2000	GOOD
3001	3001-b	200	400	GOOD

Hope this is understandable for you guys.

Regards,

Aissam

sum function in script

Tue, 22 Dec 2009 21:50:41 GMT

Try the following Script

Table_A:

LOAD

Contract,

[Sub-contract],

ForcastTotalCost,

ForecastActualCost

FROM DB;

Table_B:

NOCONCATENATE LOAD

Contract,

if(sum(ForcastTotalCost)>sum(ForecastActualCost),'GOOD','BAD') as Result

FROM DB

Group By Contract;

sum function in script

Wed, 23 Dec 2009 21:23:44 GMT

Thanks for your reply. But the Group By gives problems. I receive the following error message when I run the script. Do you know what is wrong with this script?

Aggregation expressions required by GROUP BY clause

LOAD

JC.CONTRACT_JOB,

JC.OpenPoTotal,

JC.LABOR_ACTUAL,

JC.LABOR_ACTUAL as LaborActual,

JC.BURDEN_ACTUAL,

JC.LABOR_EST,

JC.BURDEN_EST,

JC.ENG_ACTUAL,

JC.ENG_ACTUAL as EngActual,

JC.EXT_COST_ACTUAL,

JC.ENGINEERING_EST,

JC.MAT_ACTUAL + JC.EXT_COST_ACTUAL as MaterialProjects,

JC.MAT_ACTUAL,

JC.MAT_ACTUAL + JC.EXT_COST_ACTUAL as MatActual,

JC.MAT_ACTUAL + JC.EXT_COST_ACTUAL + JC.LABOR_ACTUAL + JC.ENG_ACTUAL as TotalCostActual,

JC.MATERIAL_EST,

JC.MATERIAL_EST - JC.FREIGHT_EST as MatForcast,

JC.MATERIAL_EST - JC.FREIGHT_EST + JC.LABOR_EST + JC.ENGINEERING_EST as TotalCostForecast,

if((JC.LABOR_ACTUAL+JC.ENG_ACTUAL+JC.MAT_ACTUAL +JC.EXT_COST_ACTUAL+JC.OpenPoTotal) > (JC.MATERIAL_EST - JC.FREIGHT_EST +JC.LABOR_EST + JC.ENGINEERING_EST),

(JC.LABOR_ACTUAL+JC.ENG_ACTUAL+JC.MAT_ACTUAL+ JC.EXT_COST_ACTUAL+JC.OpenPoTotal),

(JC.MATERIAL_EST - JC.FREIGHT_EST +JC.LABOR_EST +JC.ENGINEERING_EST)) as TotalActualCostRedsheetContractLevel

RESIDENT JC

Group By JC.CONTRACT_JOB

sum function in script

johnw — Thu, 24 Dec 2009 00:00:04 GMT

Remove that group by. It doesn't belong on that load. Add a NEW load after it. Unfortunately, you didn't use the same field names in your example as you do in your actual script, so I'm just guessing at which field names you want in the second table, but you want to do exactly what he said but with your field names:

Table_B:
NOCONCATENATE LOAD
JC.CONTRACT_JOB,
if(sum(TotalCostForecast)>sum(TotalCostActual),'GOOD','BAD') as Result
RESIDENT JC
GROUP BY JC.CONTRACT_JOB;