Add future months to table (forecasting in script)

[Anonymous]

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:

publisher	m-y	clicks	forecast
xyz.com	Dec-2017	1000	###
xyz.com	Jan-2018	1200	###
xyz.com	Feb-2018	1300	###
xyz.com	Mar-2018		###
xyz.com	Apr-2018		###
xyz.com	May-2018		###
abc.org	Dec-2017	2200	###
abc.org	Jan-2018	2100	###
abc.org	Feb-2018	2000	###
abc.org	Mar-2018		###
abc.org	Apr-2018		###
abc.org	May-2018		###

These are the two tables I'm working with:

publisher	m-y	clicks
xyz.com	Dec-2017	1000
xyz.com	Jan-2018	1200
xyz.com	Feb-2018	1300
abc.org	Dec-2017	2200
abc.org	Jan-2018	2100
abc.org	Feb-2018	2000

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.

[justinvchiang]

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]

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]

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

[Qrishna · Accepted solution]

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:

[Anonymous]

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