How to calculate AVG of past months within a table?

KNIME Analytics Platform
1

Hi, I have a table similar with the following columns:

City, Sales, Date

Dates include only first day of months from the last 5 years.

I want to end with a table with the following columns.

City, Past 6 months average sales, % of Past 6 months average sales vs same past 6 months of year ago, Past month, Past month -1, Past month -2… and so on.

I imagine I have to calculate the average of the dates and somehow concatenate them below my original table so I would end with something like this.

City | Sales | Date
NY | $100M | 01/01/18
NY | $100M | 01/02/18
NY | $100M | 01/03/18
NY | $100M | P6M

But I don’t know if that’s the structure I require to later pivot either in knime or visualization software like spotfire/tableau/powerBI and I have no clue how to start calculating the averages based on date.

Can anyone help?

Thanks a lot in advance!

2

I think the attached does something like what you want, or at least gives you the basis of getting to what you want. Timeseries.knwf (28.5 KB)

The main parts are:

  • Loop by city
  • Sort by date, and take moving average
  • Calculate date of -1 year and join back

image
image855×281 16.4 KB

Apologies for the somewhat wild sales figures in the example!

Steve

4 Likes
3

Worry not, sometimes our sales data looks just like that! haha

I think that loop + Moving Aggregation will give me exactly what I need, thanks Steve!

Giordan

2 Likes
4