Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Add future months to table (forecasting in script)

Hi all, this is what I'm trying to do but if you know of a better way to accomplish my end result I'm open to suggestions.

I have a table containing traffic data to a publisher over time. I'd like to add rows to the table with future months so that I can create a forecast column based on the trend of actual data. Future months for each publisher wouldn't have actual data, but once they exist in the table, it will calculate the forecast column across all rows. The table below is what I'm trying to get to:

publisherm-yclicksforecast
xyz.comDec-20171000###
xyz.comJan-20181200###
xyz.comFeb-20181300###
xyz.comMar-2018###
xyz.comApr-2018###
xyz.comMay-2018###
abc.orgDec-20172200###
abc.orgJan-20182100###
abc.orgFeb-20182000###
abc.orgMar-2018###
abc.orgApr-2018###
abc.orgMay-2018###

These are the two tables I'm working with:

publisherm-yclicks
xyz.comDec-20171000
xyz.comJan-20181200
xyz.comFeb-20181300
abc.orgDec-20172200
abc.orgJan-20182100
abc.orgFeb-20182000

future m-y
Mar-2018
Apr-2018
May-2018

As my first step, I created that "future m-y" table thinking I can concatenate that onto the main table somehow, but if you think there's a better solution out there, I'm all ears. Thank you in advance.

1 Solution

Accepted Solutions
krishna_2644
Specialist III
Specialist III

Ben,

PSA for one of the method.

if you are using master calendar and other tables,let me know the fields so that i can recode this qvw.

output:

1.PNG

View solution in original post

5 Replies
justinvchiang
Contributor III
Contributor III

I would autogenerate it as part of the load script.  Something like the below ought to do the trick:

LET vMinDate = NUM(Date#('2018-03-01', 'YYYY-MM-DD'));

LET vMaxDate = NUM(Date#('2018-06-01', 'YYYY-MM-DD'));

tmp_CALENDAR:

LOAD Date($vMinDate) + RowNo() - 1) as tmp_Date

AUTOGENERATE

            $(vMaxDate) - $(vMinDate) + 1;

future_m-y:

OAD Year(tmp_Date) as Year

         , Month(tmp_Date) as Month

RESIDENT tmp_CALENDAR;

Anonymous
Not applicable
Author

Hey Justin, thank you for your reply! I think I had done something similar before by adding the future 3 months to the master calendar and they did show up on the front end, but I think I need them in the same table acting like the other months for each publisher (but just no actual clicks) so I can calculate the forecast values.

juraj_misina
Luminary Alumni
Luminary Alumni

Hi Ben,

you definitely need it in the same table and you also need other dimension values (like publisher for example). I would autogenerate all periods I need (past + future) and do a cross join (outer join) with all dimensions (publisher in your case) and then left join actuals to this table. So you will have all publishers for all necessary periods and actual data for past periods. You can then calculate forecast for future periods.

Hope this helps.

Juraj

krishna_2644
Specialist III
Specialist III

Ben,

PSA for one of the method.

if you are using master calendar and other tables,let me know the fields so that i can recode this qvw.

output:

1.PNG

Anonymous
Not applicable
Author

Thank you Juraj for summarizing what needed to be done and thank you Krishna for the script! Such a simple solution and worked perfectly!

Ben