Extend the Date field for the forecast (add 5 months)

[RK121]

Hello, 

Could someone help me with a script for extending date for my sales Forecast.

I have DATE field (MM-DD-YYYY), and my sales stop on 08-14-2022.I would need to extend till the end of the year.

For example:

Date                    Sales

08-11-2022      42,355

08-12-2022      12,456

08-13-2022      5,345

08-14-2022      11,345

08-15-2022      0

08-16-2022      0

08-17-2022      0  ...

...

12-31-2022      0

[avinashelite]

Create a master calendar with the required date frame .

Check the below link

https://community.qlik.com/t5/QlikView-App-Dev/How-to-Create-Master-Calendar/td-p/103109

[RK121]

I have autogenerated new data to show the forecast for the next 4 month, but this only works for the full dataset. As soon as I filter any field, the future data for linear regression disappears. Does anyone know what can be done in the table settings?

my new script tab:

Let vMaxDate = Num(Date#('12-01-2022','MM-DD-YYYY'));
Let vMinDate = Num(Date#('05-01-2022','MM-DD-YYYY'));

Temp:
LOAD $(vMinDate) + IterNo() as Temp_DT
AutoGenerate 1
While
$(vMinDate) + IterNo() <= $(vMaxDate);

Temp2:
LOAD
If(Temp_DT <= Today(), Date(Temp_DT,'MM/DD/YYYY'), Null()) as UPDATE_DT,

Date(MonthStart(Temp_DT),'MM-YYYY') as UPDATE_DT_TEST
//Date(Temp_DT,'MM/DD/YYYY') as Temp_DT_Date

RESIDENT Temp;

Example: 

[avinashelite]

2 things .

1.Your data is related to the future dates or not ? if not then based on the selection it gets auto disabled because the data is not linked . To avoid this you need to join your master calendar with the data set so that the data will get linked to the future dates 

2. If you have already done the step 1 , then go to chart setting > Presentation > un-check suppress Zero-values 

[RK121]

We have subroutine to create a Calendar, how would you recommend to update the script in this case?

CALL SUB_Load_Calendar.RegisterColumn('CAL_Serial_DayNo');
CALL SUB_Load_Calendar.RegisterColumn('CAL_Serial_WeekNo');
CALL SUB_Load_Calendar.RegisterColumn('CAL_Serial_MonthNo');

Call SUB_Load_Calendar('UPDATE_DT', 0, 0 ,0, 0);

TN_ACTIVITY_MASTER_CALENDAR:
LOAD UPDATE_DT
, CAL_Serial_DayNo AS [TN Activity Day No]
, CAL_Serial_WeekNo AS [TN Activity Week No]
, CAL_Serial_MonthNo AS [TN Activity Serial Month No]
// , CAL_IsFullWeek AS ORD_COMPL_CAL_IsFullWeek
// , CAL_IsFullMonth AS ORD_COMPL_CAL_IsFullMonth
, CAL_Day_Label_Medium AS [TN Activity Day]
, CAL_YearMonth_Label_Short AS [TN Activity MTD]
, CAL_FullYearMonth_Label_Short AS [TN Activity Month]
, CAL_FullWeek_Label_Medium AS [TN Activity Week]
// , CAL_Year AS [Order Completed Year]
RESIDENT MASTER_CALENDAR;
// Drop Fields CAL_Serial_MonthNo, CAL_Serial_WeekNo;
DROP TABLES MASTER_CALENDAR, MASTER_CALENDAR_WEEK_INTERVALS;

My schema looks like this now: