Forecast Data with Blank fields

dia2021 — Tue, 18 Jan 2022 17:39:38 GMT

Hi All,

I have up to date dataset showing data till today. I am trying to create 12 months of forecast data. I have achieved that but the problem is that it needs to keep on going forward 12 months; however the script is working only for next 12 months only. I want it to keep on increasing as the monthly data comes in. I have created a master calendar for this.

TempMaxDate:
Load
Max(Date) as MaxDate
Resident AMData;

LET vMaxDate = Peek ('MaxDate',0,TempMaxDate);
LET vForecastDate = $(vMaxDate)+365;

Trace $(vMaxDate);
Drop table TempMaxDate;
NoConcatenate

AM_Temp:
Load *
Resident AMData;

Concatenate
TempDates:
LOAD $(vMaxDate)+IterNo() AS Date
AUTOGENERATE (1)
WHILE $(vMaxDate)+IterNo()<= $(vForecastDate);

AM:
Load
MKey,
Date,
year(date([Date],'MM/DD/YYYY')) as AM_Year,
month(date([Date],'MM/DD/YYYY')) as AM_Month
//AutoNumber(R1_Year,'YearRank') as Year_Rank

Resident AM_Temp;

Drop tables AM_Temp, AMData;

Any suggestions. Thank you in advance!

topic Forecast Data with Blank fields in App Development

Forecast Data with Blank fields