Forecast based on last 2 weeks

[FilipBeerwulf]

Hi Guys,

I’m new to KNIME, used Alteryx before, but quite happy so far with KNIME. might be able to introduce it to my company.

I am trying to do a “forecast” for warehouse space. Basically, I need to forecast stock 1 week ahead, for every week day. The forecast is the average of the previous 2 weeks days. For example, if i forecast next Monday, it has to be the average of the previous 2 Mondays and so for every week.

I have 2 main questions:

• is there a way to generate future dates?
• is there a way to moves sales that happen Friday after a certain hour to Monday?

Having these 2, i believe I can just do an average formula of the last 2 weekdays, no?

Example of input/output

Date	WeekDay	Product A	Product B
02/08/2021	Monday	300	500
03/08/2021	Tuesday	150	300
04/08/2021	Wednesday	180	320
05/08/2021	Thursday	220	350
06/08/2021	Friday	240	280
07/08/2021	Saturday	0	0
08/08/2021	Sunday	0	0
09/08/2021	Monday	330	530
10/08/2021	Tuesday	180	330
11/08/2021	Wednesday	210	350
12/08/2021	Thursday	250	380
13/08/2021	Friday	270	310
14/08/2021	Saturday	0	0
15/08/2021	Sunday	0	0
16/08/2021	Monday	315	515	Forecast last 2 Mondays
17/08/2021	Tuesday	165	315	Forecast last 2 Tuesdays
18/08/2021	Wednesday	195	335	Forecast last 2 Wednesdays
19/08/2021	Thursday	235	365	Forecast last 2 Thursdays
20/08/2021	Friday	255	295	Forecast last 2 Fridays
21/08/2021	Saturday	0	0
22/08/2021	Sunday	0	0

Thanks a lot fot the help.

Filip

[bruno29a]

Hi @FilipBeerwulf and welcome to the Knime Community.

Absolutely, Knime can do the 2 things you mentioned.

To generate future dates, you can use the Create Date&Time Range node.

And to move sales that happen Friday after a certain hour to Monday, we can rephrase this as changing the date of some sales, and if you want to change dates, you can use the Date&Time Shift node.

[FilipBeerwulf]

Hi Bruno,

thank you! Managed shift orders from Friday and weekend to Monday.

I’m not sure how to create the forecast now. I basically need to create,:

1. Forecasted Monday based on the average of previous 2 Mondays, Tuesday forecast based on average of previous 2 tuesdays etc.
2. Generate the right dates automatically.

I can share the workflow if you wish, but i’m referring to the “Forecast last 2…” in the table above.

Not very sure how to approach it. Maybe you have a suggestion.

Thanks again,
Filip

Is there a rule based multi formula, or something like that?

[jorgemartcaam]

I am stuck with basically the same problem!

[bruno29a]

Hi @FilipBeerwulf , first of all, please use the @ sign when tagging a user, or the user will not get notified

For your request, how far ahead are you generating the dates?

If it’s just 1 week, something as simple like this works:

Input data:

Output data:

Here’s the workflow:
Forecast based on last 2 weeks.knwf (12.6 KB)

Anything more than a week will have to run through a loop

[jorgemartcaam]

Hi @bruno29a!
Could you check this one out?

Lag Time Series Auto-Prediction Training Meta node

Hi there, I want to forecast some data. I have been playing and reading about ARIMA but I find myself not being able to implement it, so I changed direction. I am currently using the meta nodes that Knime has included in the time series section and I have been able to succesfully (with some error) implement a regression. [image] Now I want to further deepen into the forecasting. If the last day is “190” I want to be able to extend the forecast to, for example, 290. Something like this. [im…

[FilipBeerwulf]

@bruno29a Hi Bruno, I am connected straight to a database, so i can’t have the same setup as you.

I have maanged to do it differently, the only issue is Node42, i need to update that formula manually every time. Do you think it’s possible to automate?

What do you think about the setup? Thank you

KNIME_project3.knwf (68.9 KB)

[bruno29a]

Hi @FilipBeerwulf

@FilipBeerwulf

I am connected straight to a database, so i can’t have the same setup as you

I am not sure I understand why you can’t have the same setup. Whatever you read from a db table via Knime will end up as a Knime table, which is basically the same setup.

So, you just need to integrate the workflow in your workflow.

I tried looking into your workflow, unfortunately your data table file was not included, so I could not go far in the workflow as some nodes complained about having no input data.

[Daniel_Weikert]

Interesting,
First thought in mind was to use 2 Lag columns but this would be difficult if you have to many product columns
br

[FilipBeerwulf]

@bruno29a Hi bruno, I have fixed the workflow so you cna see what I mean.

1. Node42 - these are the weeks i haverage, is it possible to automate? I mean as numbers change, i don’t want to update them manually, I’d like to always average previous 2 weeks.

2. Is it possible to avoid averaging with 0? Thanks, Filip

KNIME_project.knwf (3.6 MB)

[bruno29a]

Hi @FilipBeerwulf , thank you for including the data.

If by “automating” Node42 you mean to do the average without having to manually change the column names, yes, it can be done.

From what I see in your workflow, you don’t really care about the week number in your final result, you just want to know which data is from 2 weeks ago and which data is from last week (1 week ago). Based on this, I did a little trick where I modify the week values to always be 1 or 2 before the pivot, that way you will always have 2 columns with name “1” and “2”, which you can then refer to in your Math Formula:

And here’s the formula that I used:

$Week$ - COL_MIN($Week$) + 1

After that, the data looks like this:

…

And after pivoting, you will always end up with columns “1” and “2”, instead of the dynamically changing week numbers:

Now, you can safely point to Column “1” and “2” all the time as the dynamic change happened before that:

You will not have to change the weeks to be averaged anymore.

Also, I see you are doing a Double To Int, however, because you are doing the Concatenate with other columns that are of type double, the columns you converted to int are back as double. If that’s what you want, then it’s fine. Otherwise, you might want to move the Double To Int node after the Concatenate to keep these columns as int in the final results.

For your second question, I don’t understand. To help clarifying, what do you want to see for the following cases?

+------+------+-----------------------+
| Col1 | Col2 | CurrentAverageResults |
+------+------+-----------------------+
|    0 |    0 |                     0 |
|    1 |    0 |                   0.5 |
|    0 |    1 |                   0.5 |
+------+------+-----------------------+

What average results do you want instead?