Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Forecast Analysis Based on Rolling Relative Date

Chasebalke
6 - Meteoroid

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 WeekForecastSales
A123000123448-3100110
A123000123449-210090
B123000123448-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

2 REPLIES 2
RolandSchubert
16 - Nebula
16 - Nebula

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

 

Chasebalke
6 - Meteoroid

@RolandSchubert Yes, that seems to work perfectly for what I need, thank you!

Labels