# Forecast based on last 2 weeks

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

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.

3 Likes

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?

2 Likes

I am stuck with basically the same problem!

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

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

1 Like

Hi @bruno29a!
Could you check this one out?

@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)

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.

1 Like

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

@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)

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?

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.