I am relatively new to KNIME and struggling with the best way to aggregate values over time.
What I am attempting to do is aggregate values 12 periods in the past for each unique location. Attached screenshot should provide further clarity.
E.g. say for location 200, year 2017 and month 1, how can I aggregate the 4 columns of values for 11 periods prior (inclusive of current month to derive 12 months values)? Idea is to derive a moving average to smooth results over time.
Thanks for your assistance.
Have you already seen the GroupBy node? This might be helpful to aggregate the value columns via the "Manual Aggregation" Tab (select Sum aggregation method).
to achieve this you can either use the GroupBy or the Moving Average node. Do you need the moving average just for one specific or every single point in time? If you need it for a single one, you can use the GroupBy. If you need it for every point in time, you should give the moving average a try.
No matter which one you use, you have to do some preprocessing. If you go for the GroupBy, you have to filter the data such that it only includes records from the relevant timeframe (in your example February 2016 till January 2017). Then you can group by locations and select mean as aggregation function for your four columns. If you go for the Moving Average node you have to split your data based on location, such that you get a separate table for every location. Next you use the Moving Average node for your four columns you want to aggregate. Make sure that your data is sorted properly before you use the moving average.
Hope that helps.