Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)?
No it doesn´t, I define it with my statement. I just need it as an intermediate step for the calculation.
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 ....
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
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
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?
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?
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',...);
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