Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
bgoldberg
New Contributor III

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
Valued Contributor III

Re: Add future months to table (forecasting in script)

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
Highlighted
justinvchiang
New Contributor III

Re: Add future months to table (forecasting in script)

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;

bgoldberg
New Contributor III

Re: Add future months to table (forecasting in script)

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.

Luminary
Luminary

Re: Add future months to table (forecasting in script)

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
Valued Contributor III

Re: Add future months to table (forecasting in script)

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

bgoldberg
New Contributor III

Re: Add future months to table (forecasting in script)

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