Calculating averages for different time periods

[muns]

Hi everyone,

I have a basic Alteryx question from an excel perspective.  In my simplified example below, I would like to populate a record with the average for a specific period (e.g. the last 3 years). What is the most efficient and dynamic way to do this please?

Many thanks,

Mark

[Jean-Balteryx · Accepted solution]

Hi @muns !

Correct me if I misunderstood. What you want is a moving average, let's say over the last three years. For example, for 2019, you'll get the average of 2017, 2018 and 2019 values.

To achieve this, you can use a Multi-Row Formula Tool.

I attached an example workflow.

Also, here is a blog post explaining how to do it : https://www.thedataschool.co.uk/jeremy-kneebone/calculating-moving-average-alteryx/

Don't hesitate to ask for more details.

Have a great day !

[muns]

Thanks very much. I hadn't learnt yet that you can change the number of rows in the multi-row formula! Easy now you've showed me - thanks!

[RolandSchubert]

Hi @muns ,

you could use the Multi-Row formula tool to calculate averages. It's not really a dynamic approach, but can easily be modified (e.g. different number of years). I think an important question is, how to handle the first year, when there is no prior year available - a condition could help (e.g. if only one previous year available, calculate averageof two years). I've attached a sample workflow, hope, this is helpful.

Best regards

Roland

[Jean-Balteryx]

You're welcome @muns !