topic Re: QV Script Sum problem in QlikView

QV Script Sum problem

aliyoung92 — Wed, 25 Nov 2020 16:16:04 GMT

I'm trying to sum a budget number as it comes out in numerous rows and I need it all grouped so it can be added to another table.

I've been using sum but this doesn't seem to work.

Below is the script i'm trying but it keeps telling me it is invalid, could someone please help?

TEMP:

SQL SELECT

AccountLevel3,

ActivityLevel1,

ActivityLevel2,

ActivityLevel3,

ActivityLevel4,

ActivityLevel5,

amount,

"amount_variant",

client,

CostCenterLevel2,

"period_month",

"period_year"

FROM TEST

WHERE CostCenterLevel2 <> '111' AND CostCenterLevel2 <> '112'

AND "period_year" >= '2016'

AND AccountLevel3 <> '9000' AND AccountLevel3 <> '9200' AND AccountLevel3 <> '9500'

AND "amount_variant" = 'BR';

Budget:

Load

AccountLevel3 as Accountlvl2,

CostCenterLevel2 as IE_Unit,

amount,

SUM(amount) as Budget,

"period_month",

"period_year",

"period_month" & "period_year" as FY_LUP

Resident TEMP

Group By

AccountLevel3, CostCenterLevel2

;

Drop Table TEMP;

Re: QV Script Sum problem

Anonymous — Wed, 03 May 2017 14:35:18 GMT

If this is really your script you're missing a few things. Something like this would be closer.

TEMP:

load *

;

SQL SELECT

AccountLevel3,

ActivityLevel1,

ActivityLevel2,

ActivityLevel3,

ActivityLevel4,

ActivityLevel5,

amount,

"amount_variant",

client,

CostCenterLevel2,

"period_month",

"period_year"

FROM TEST

WHERE CostCenterLevel2 <> '111' AND CostCenterLevel2 <> '112'

AND "period_year" >= '2016'

AND AccountLevel3 <> '9000' AND AccountLevel3 <> '9200' AND AccountLevel3 <> '9500'

AND "amount_variant" = 'BR';

Budget:

load *,

"period_month" & "period_year" as FY_LUP

;

Load

AccountLevel3 as Accountlvl2,

CostCenterLevel2 as IE_Unit,

SUM(amount) as Budget,

"period_month",

"period_year",

Resident TEMP

Group By

AccountLevel3, CostCenterLevel2,period_month,period_year

;

Drop Table TEMP;

Re: QV Script Sum problem

Anonymous — Thu, 04 May 2017 11:14:57 GMT

Hi,

Please try like this below.

I have created dummy application to explain your problem

Sample code: Sample Data

RESULT may look like

when you are using sum(fact) in your script it should group by with all its dimension value. Also you can use it in preceding load function please refer below thread for more information

IF and SUM in script load from SQL

Note: if you are adding more than 1 dimension values in your script you need to include it in your group by function