Forecast Analysis Based on Rolling Relative Date

Question

Hi,

I am doing forecast analysis, I need to to be able to determine two things. First, let me describe the data a bit.

I have forecast and actuals time series data, grouped at the lowest level we do planning at (plant, item#, customer, week). I have also built a relative week table to today, which is included in the data set. Example Below:

PlantItem#CustomerWeekRelative WeekForecastSalesA123000123448-3100110A123000123449-210090B123000123448-39555

Now, what I need is these two things:

1.Rolling 5 week average of actual sales (average for the previous 5 weeks at that grouping level)

2.Rolling weeks -5 to +5 average forecast (average forecast based on 5 weeks back and forward at the lowest grouping level).

In excel, I would probably use some form of AverageIfs(), making sure to aggregate at the level I need. Can this be done in alteryx?

Any input would be greatly appreciated. Thanks!

Chase

RolandSchubert · Accepted Answer

Hi @Chasebalke ,

a Multi-Row Formula tool should do the job (exactly there will be two of them, one for Actual, the other one for Forecast).
Set "Num Rows" to "5", so you will get 5 periods back and forward (if available).

The Average function calculates the average value as you need.

If you select "Group by" Plant, Item and Customer calculation will be performed for the detail level you need.

You should sort by Plant, Item, Customer and Week before to have rows in required order.

Does this help?

Best,

Roland