Calculating averages for different time periods

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

Preparation

Accepted answers

Jean-Balteryx

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 !

Moving_Average.yxmd

All comments

Jean-Balteryx

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 !

Moving_Average.yxmd

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

MovingAverage.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703