In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

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

Calculating averages for different time periods

muns
7 - Meteor

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?

 

 

muns_1-1575287433107.png

 

Many thanks,

Mark

4 REPLIES 4
Jean-Balteryx
16 - Nebula
16 - Nebula

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
7 - Meteor

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
16 - Nebula
16 - Nebula

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
16 - Nebula
16 - Nebula

You're welcome @muns !

Labels
Top Solution Authors