I’m trying to calculate rolling sum last 7 days. The solutions I’ve found on this topic suggest using the Moving Aggregation node however, that node does a cumulative sum based on number of rows not difference in dates. My dataset has missing dates in between for eg. 22-10-2022 and the next row would be 24-10-2022. I managed to do this relatively easily in Power BI but can’t figure it out in KNIME. Please see table below with rolling sum (last 7 days) column:
See this wf rolling_sum_last_7_days.knwf (96.8 KB) Based on the first and last date in your input file, all dates between those are created. After joining them together, the use of the Moving Average node is straight forward.
This is a good attempt! But unfortunately in the final table we don’t get an average for the 1st 7 days. If you see my solution which I managed through power bi we get a rolling average/sum from day one. Anyway we can manage the first 7 days? Thanks nonetheless you got me somewhere
Hey @badger101! I was wondering if you could help me out with this? The solution Hans has provided works but doesn’t give a rolling avg/sum for the first 7 dates.
Hi @Fahd_Aly_K , at first I thought why did you mention me since I didn’t engage on this post. Then I looked at your profile and I recalled the other post I helped solve
I actually have taken a look at what a rolling sum is earlier today before you tagged me. But there are confusing info on the internet. I looked also at your screenshot and I can’t seem to know how to read the pattern of the data. I think I’m not the right one for this task I’m afraid. I think you’ll have to wait for HansS or others to amend the already available workflow for you. Plus, I also won’t be writing on the forum much for the next few days at least. I’m sure you’ll find the solution, cause the community here is great!
