# Recursive Row Loop for Sales Forecasting

Hello, I’d like to use a recursive row loop process to forecast future daily sales. I’m including a workflow with 2017-2018 dates and random sales generated. I’d like to have a loop that creates 2019-01-01 thru 2019-12-31 using a five-part moving average process. Example: to generate 2019-01-01 sales data row, take the following prior sales rows and average them:

12/25/18
12/18/18
12/11/18
12/4/18
1/2/18

Can anyone build out this workflow quick from the attached starting data? Thanks!

Recursive Row Loop Forecasting.knwf (12.9 KB)

Hi @t2jr0385,

I am not sure if I understood exactly what you want to do. I have looked into your workflow but I don’t get how you determine that 2019-01-01 should be generated by those rows:

Could you explain it to me? I am happy to help!

Simon

Hi @t2jr0385 -

Here is something you can try. There’s no looping involved, it’s just calculating an average based on the past 5 weeks of data. It’s probably not exactly what you want, but maybe it will help.

AverageLaggedColumnExample.knwf (15.1 KB)

1 Like

Thanks, Scott. This is what I started with as a first version for predicting future sales. For every single day of business, I took the past four weeks for that same day along with the day to predict from 52 weeks ago.

To use this five-week averaging method, I re-forecast 2018 dates using the averaging method and then did a date shift to move each date to a 2019 date. The problem with shifting rather than creating new rows is the earlier part of the 2018 year pulled down sales predictions for early 2019 too much. This is why I’d like to take the last four weeks of 2018 and generate 2019 rows forward rather than date shift.

Does that help clarify the problem at hand?

Thanks!

Simon. Thanks for the replies. I replied to Scott in attempt to better explain the problem at hand. Looking to generate new 2019 sales forecast based on 2018 rather than date shift 2018 dates => 2019 dates which is the first version of what I’ve done.

Thanks for any input you can provide on creating new rows based on an average of prior rows (creating lag columns for each prior value to include in forecasting the future row.

I would like to point you to a few resources where they tried to predict future sales at a german retail chain on Kaggle. Maybe you might get some useful insights.

One thing to consider might be special things like (regional) holidays or events like easter that will not be at the same date every year, and the holidays might not only affect the day itself but the days before and after. You might want to take that into account.

Trying to predict future sales on Kaggle

Interview with winner

Impute Data with R’s Amelia
(I have an article about Amelia and time series imputation somewhere in the internal forum at the office, might look that one up)

1 Like

@mlauber71, thanks for the input. I’ll check out these resources.

I’d still like to see if anyone can solve this very rudimentary method for ‘forecasting’ prior diving into any R integrations in KNIME. Here’s an attempt to better explain the problem:

I’m looking to create a sales forecast by averaging together a few weeks of data.

date sales
week 1 123
week 2 354
week 3 569
week 4 734

I use the lag node to get prior weeks on the same row:

date sales sales lag 1
week 1 123 n/a
week 2 354 123
week 3 569 354
week 4 734 569

What I’d like to do is take the average of these sales from one row and have KNIME generate a new row and continue the pattern for x rows:

• = forecast data based on simple averaging

date sales sales lag 1
week 1 123 n/a
week 2 354 123
week 3 569 354
week 4 734 569

week 5* 652 734
week6* 693 652

and so on…

Can this be done?

Well, you asked for it. Might be a little bit over engineered … But I still wonder how to handle a loop if I have to reflect on something and ‘remember’ it - yes I can append something at the end of a loop, but sometimes my first idea is to store the intermediate results.

You should really check if you just want such a simple prediction, but anyway, here it comes.

Here I use 2 weeks to predict, you can change that by setting the variable.

kn_example_week_forecast.knwf (89.5 KB)

1 Like

Hi

sorry for finding this so late.

I have aworkflow where I did implement this using the recursive loops. 2016_01_13_moreFutureTimeSeriesPrediction.knwf (46.4 KB)

I actually wonder why I never uploaded it to the examples server.

If you want to see me explaining the recursive loops, I did this at our User Group Meeting in Zurich once upon a time ;). https://www.youtube.com/watch?v=0zhDVBd2ihk

The UGM is the former version of our KNIME Summits.

Cheers, Iris

2 Likes

Hi there!

Not sure this forecasting will give you any meaningful numbers for later weeks. Take a look at the numbers and picture:

I suggest for beginning to take a look at Moving Aggregation or Moving Average node which I used. It will give you only a “forecast” for next week but as well it will give you indication how good is it because you can compare it

Anyways I have created a workflow for your original request so take a look if you wish.
2019_01_17_Sales_Forecasting_with_Recursive_Loop.knwf (32.5 KB)

Br,
Ivan

1 Like

Thank you @ipazin and @Iris.

I know it’s a simple forecast. What I’m actually doing is four recent weeks plus the forecast week or day last year. So an average of five weeks with a 7,14,21,28, and 364 lag. I plan to improve this forecasting method eventually but wanted to start with this as I’m also building a pipeline and basic BI reporting system at the same time.

I think both of your inputs are incredibly valuable and I look forward to playing around with them.

Thank you again for the timely input!

2 Likes

… makes mental note to use Recursive Loops in the future …

1 Like