Conditional sums in script

[#1]

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

[maxgro]

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)?

[#3]

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

[Anonymous]

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 ....

[sasiparupudi1]

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

[Gabriel]

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 ....

Also read Preceding Load

By HIC

[#7]

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?

[#8]

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?

[Anonymous]

hi,

Try

load *;

SQL select * FROM ....

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

or

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',...);

[sasiparupudi1]

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