topic Re: Conditional sums in script in QlikView

Conditional sums in script

Fri, 11 Sep 2015 14:44:59 GMT

Good afternoon,

My data source is a SQL DB with the following line structure

Year / Month / CostCenter / Account / Value / ValueType

2015 / 005 / 4711ABCD Global Head of Marketing / 690037 Travel Expenses / 250.000 EUR / Budget

The value types are Actuals / Budget / Forecast. While Actuals are split by month, Budget and Forecast aren´t i.e. technically they come on month 012.

I would like to split Budget and Forecast to months. A good way to do so is to use actuals for (financially) closed months and distribute the remaining forecast (i.e. yearly forecast - yearly actuals) across the remaining months.

I do

set LastClosedMonth = 8;
Set counter=8;

Do while counter < 12

load

.....

if (Area = 'Actuals', sum (Value),0) as ActualYTD,
if (Area = 'Forecast', sum (Value),0) as ForecastYTD,

(ForecastYTD - ActualYTD) / (12 - $(LastClosedMonth)) as Forecast,

....

SQL select * FROM ....

But get the error message that ForecastYTD, doesn´t exist...

Thanks in advance!

Leonardo

Re: Conditional sums in script

maxgro — Fri, 11 Sep 2015 14:51:58 GMT

if you ran the same select and replace the load with load *

load *;

SQL select * FROM ....

and then open the table viewer (CTRL+T) does it exists a ForecastYTD field (pay attention to case)?

Re: Conditional sums in script

Fri, 11 Sep 2015 14:55:18 GMT

No it doesn´t, I define it with my statement. I just need it as an intermediate step for the calculation.

Re: Conditional sums in script

Anonymous — Fri, 11 Sep 2015 17:51:28 GMT

hi leonardo,

try:

set LastClosedMonth = 8;
Set counter=8;

Do while counter < 12

load *,

(ForecastYTD - ActualYTD) / (12 - $(LastClosedMonth)) as Forecast,

....

load

.....

if (Area = 'Actuals', sum (Value),0) as ActualYTD,
if (Area = 'Forecast', sum (Value),0) as ForecastYTD,

....

SQL select * FROM ....

Re: Conditional sums in script

sasiparupudi1 — Fri, 11 Sep 2015 17:56:48 GMT

Basically you are getting the error becuase you created a calculated filed.

Try like this

set LastClosedMonth = 8;
Set counter=8;

Do while counter < 12

load

.....

if (Area = 'Actuals', sum (Value),0) as ActualYTD,
if (Area = 'Forecast', sum (Value),0) as ForecastYTD,

(if (Area = 'Forecast', sum (Value),0) - if (Area = 'Actuals', sum (Value),0)) / (12 - $(LastClosedMonth)) as Forecast,

....

SQL select * FROM ....

hth

Sasi

Re: Conditional sums in script

Gabriel — Fri, 11 Sep 2015 23:31:55 GMT

Hi,

You possibly have to use Preceding Load logic here.

Create ForecastYTD first then use ForecastYTD in the table immediately above table where you create ForecastYTD

set LastClosedMonth = 8;

Set counter=8;

Do while counter < 12

LOAD

load

,(ForecastYTD - ActualYTD) / (12 - $(LastClosedMonth)) as Forecast

;

LOAD

if (Area = 'Actuals', sum (Value),0) as ActualYTD,

if (Area = 'Forecast', sum (Value),0) as ForecastYTD

;

...

SQL select * FROM ....

Re: Conditional sums in script

Mon, 14 Sep 2015 08:34:35 GMT

Thanks Neetha. Following your suggestion, I get the error message:

"Field not found - <Area>

SQL select * FROM GISdb.dbo."SAP_SO_Report" where CD = 'D' and Year > 2013 and (Area = 'FCYEAR' or Area = 'ACTYTD')"

Area is the first variable in the " if (Area = 'Actuals', sum (Value),0) as ActualYTD," clause - how come the script cannot recognize it?

Re: Conditional sums in script

Mon, 14 Sep 2015 08:35:45 GMT

Thanks Gabriel. Following your suggestion, I get the error message:

"Field not found - <Area>

SQL select * FROM GISdb.dbo."SAP_SO_Report" where CD = 'D' and Year > 2013 and (Area = 'FCYEAR' or Area = 'ACTYTD')"

Area is the first variable in the " if (Area = 'Actuals', sum (Value),0) as ActualYTD," clause - how come the script cannot recognize it?

Re: Conditional sums in script

Anonymous — Mon, 14 Sep 2015 09:01:18 GMT

hi,

Try

load *;

SQL select * FROM ....

and then open the table viewer (CTRL+T) Check if area field exists(case sensitive)

set LastClosedMonth = 8;
Set counter=8;

Do while counter < 12

load *,

(ForecastYTD - ActualYTD) / (12 - $(LastClosedMonth)) as Forecast,

....

load

.....

if (Area = 'Actuals', sum (Value),0) as ActualYTD,
if (Area = 'Forecast', sum (Value),0) as ForecastYTD,

....

where CD = 'D' and Year > 2013 and Area = {'FCYEAR' , 'ACTYTD'};// try (Area = 'FCYEAR' or Area = 'ACTYTD')

SQL select * FROM ....

another option:

SQL select * FROM GISdb.dbo."SAP_SO_Report"

where CD = 'D' and Year > 2013 and Area IN ('FCYEAR', 'ACTYTD',...);

Re: Conditional sums in script

sasiparupudi1 — Mon, 14 Sep 2015 09:09:32 GMT

if it is a variable,use it like this

if ('$(Area)' = 'Actuals', sum (Value),0) as ActualYTD,

if ('$(Area)' = 'Forecast', sum (Value),0) as ForecastYTD

hth

Sasi