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 , 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: [image] And here’s the formula that I used: [image] $Week$ - COL_MIN($Week$) + 1 After that, the data looks like this: [image] … [image] And after pivoting, you will always end up with columns “1” and “2”, instead of the dynamically changing week numbers: [image] Now, you can safely point to Column “1” and “2” all the time as the dynamic change happened before that: [image] 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?

Forecast based on last 2 weeks

KNIME Analytics Platform

jorgemartcaam August 25, 2021, 2:12pm 6

Hi @bruno29a!
Could you check this one out?