Assign week number based on rolling 7 days

Hi guys,

I have built a forecast for inventory that takes for each day of the week and average of the previous day that week. Looks something like this:

image

The issue that I have is they are running it every wednesday, so using the Extract Date&Time fields gives me 3 weeks, instead of 2.

Is there any way I can make my Week Wednesday to Wednesday? Or something like that.

Thanks and regards,
Filip

Hello @FilipBeerwulf,

not sure I follow. Can you maybe share example workflow or data to better explain issue you are encountering? (Dummy data works just fine.)

Br,
Ivan

3 Likes

Hi @FilipBeerwulf ,

I’m not sure I understand that statement. Based on the data you showed, the forecast seems to average for the past 2 weeks of that day. Can you re-confirm the rule?

Other clarifications needed:
What’s your input data? I mean when it runs every Wednesday, that means you would have an inventory count up to that day, is that correct?

And what days do you want to forecast for? For a week from that Wednesday? So starting from Thursday after that Wednesday until the next Wednesday?

Assuming that you have data up to Wednesday (or any day for that matter), and that you need to forecast for the next 7 days, you can simply do:

  1. Get current date - use Create Date&Time Range and configure using Execution Time for both start and end
  2. Retrieve records where date > (current date - 14 days) - use Row Filter
  3. Extract day of the week of these records - use Extract Date&Time Fields
  4. Then you average on group by day of the week
1 Like

KNIME_project3.knwf (65.6 KB)

Basically, forecasted Monday =Average(previous 2 mondays) and so forth for every weekday. But because I do it in the middle of the week, I get 3 weeks, which fucks up my numbers…

Your workflow has no data attached but my first idea would be using 2 lag nodes and then a column aggregator node to get the mean

2 Likes

Hello @FilipBeerwulf,

as @Daniel_Weikert said hard to tell anything without data (uncheck reset option if you would like to share workflow with data upon exporting). Can’t you try filtering week/days you don’t want for prediction based on current week/date?

Br,
Ivan

1 Like

KNIME_project3.knwf (2.5 MB)

Hi guys, sorry for the delay, here it is.

I have attached a small sample. I am looking at 2 weeks, but because I’m running in the middle of the week, I get 3 weeks using DateTime Extractor.

I’d like to be able to look at first 7 days as a week, and the next 7 days as another week, do the average and us that number in the forecast.

@ipazin @bruno29a @Daniel_Weikert

Hello @FilipBeerwulf,

the data is here but still I’m struggling with logic. Anyways you have 2 Extract Date&Time Fields node. Which one is problematic? As suggested before can’t you filter out those weeks at some point in your workflow?

Br,
Ivan

Let me explain in a few steps.

This is a forecast for a stock team. They need to communicate to send how many units per product need to be in the warehouse every day of the week. So, for next monday, we need an average of previous 2 mondays, tuesday = average(previous 2 tuesdays) etc.

Orders from Friday after 5 oclock go to next monday, so taht’s why the workflow is branchhing out in the middle. We get orders during the weekend, but the warehouse does not work.

The forecast is done every wednesday, so that date&time extractors splits 14 days in 3, which messes up with the average. I wanted split in 2. Does this make sense? Node30 is the issue

Why does everyone have a KNIME_project3 project? :slight_smile:

I’m still trying to understand what’s the typical scenario, and why we have so much data in the file. Are you forecasting for dates that have already past, or for the future week?

My understanding so far is that, and for the sake of the explanation, I will refer to current week at week0, next (future) week as week1, the last 2 weeks as week-1 (last week) and week-2 (2 weeks ago), and similarly mon-2 means Monday of 2 weeks ago, tue-1 Tuesday of last week, wed0 Wednesday of the current week which is also your current date of running the forecast, and mon1 Monday of next week. Based on this, I’m understanding that you would have this information when you are running the forecast:
image

And you want to generate data for mon1 to fri1, where:
mon1 = average of mon-1 and mon-2
tue1 = average of tue-1 and tue-2

fri1 = average of fri-1 and fri-2

Is that correct?

In a sense, you don’t really care about the data of the current week, correct? You just need the data of (current week -1) and (current week - 2), correct?

I’m going with the same logic as I came up before:

  1. Get current week number
  2. Filter data from 2 weeks ago (last week and the week before last week)
  3. Extract day of the week
  4. Average and group by day of week and subproduct

I put something together based on that that looks like this:
image

My input data, which is my Node 1 is basically the data that is the results of your Node 65:

One exception is that I added the last column as an Int:

I wanted to avoid having to run all the nodes that you have as I’m running low on memory, but this input table has the same results as your Node 65 has, so it’s essentially starting from Node 65.

Results of the forecast by subproduct:

We are currently in week 37, so the forecast was done based on data from week 35 and week 36.

After that you can filter out weekends if you don’t want weekends, but there were weekend data in weeks 35 and 36. You probably also might want to round up the numbers, I just did not know what you wanted to do with fractions, but you can figure out that part. I’m just addressing the issue of averaging data per day per subproduct of the last 2 weeks.

Also, it does not matter if you are running this on Wednesdays or any other day. It will just use the data based on (current week -1) and (current week - 2)

Here’s the workflow: Forecast next week data based on last 2 weeks.knwf (24.9 KB)

EDIT: For quick validation, here’s the total average of the last 2 weeks per day if I group by day only in the workflow:
image

Do they reflect the expected values if you did them manually?

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