Alteryx Designer Discussions

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

Moving Average via Multi-Row-Formula: How to Define Range for Average Function

Degas
6 - Meteoroid

Hi everyone,

 

I'm using the Multi-Row-Formula tool to calculate a moving average of headcount for a period of 6 days:

 

ROUND(
AVERAGE(
[Row-5:Headcount], 
[Row-4:Headcount], 
[Row-3:Headcount], 
[Row-2:Headcount], 
[Row-1:Headcount], 
[Headcount]), 
0.01)

 

That works without problem. But what do I do if I want to calculate the moving average for a period of 365 days? Do I really need to add 365 rows to the average function ([Row-364:Headcount] until [Headcount])? That would be extremely tiresome.

 

Is there a way to define a range for the average function, instead of defining every single entry?

 

Thanks a lot in advance!

 

Best,

Degas

11 REPLIES 11
AngelosPachis
15 - Aurora

Hi @Degas ,

 

A more dynamic way would be to use a multi-row formula to count the number of values you want to include in your moving average and then a running total tool to work out the sum and divide the two numbers. You can follow the same principle if you have 365 days in your dataset

 

AngelosPachis_0-1637833136300.png

 

Hope that helps,

Angelos

 

Degas
6 - Meteoroid

Hi Angelos, 

 

Thanks for your super quick reply and your help! Really appreciate it.

 

I've tried your workflow but (if I'm not mistaken) your solution only calculates the moving average per year:

 

Degas_0-1637846463639.png

 

I'm hoping to find a solution to calculate the moving average always for the current row and a number of previous rows. The moving average shouldn't be restarted once the year changes. So if I want the moving average for 3 days the value in record 32 should be 420,33 ( (638+375+248)/3 ), instead of 638.

 

I'm looking for a relatively easy way to change the number of rows that should be included in the calculation of the moving average, in case I need the moving average for 100 days, 200 days, 365 days or another period of time.

 

I hope it's clear what I mean 🙂

 

Do you have any idea for that?


Best,

Degas

Sebastiaandb
11 - Bolide

Hi @Degas,

 

I made a macro for you, should work (hopefully ;-)). I took the text input of @AngelosPachis  (thanks for that!)

 

Put the group by's on none and the control parameter on:

Sebastiaandb_0-1637852359057.png

You can fill in any N you want.

 

Sebastiaandb_1-1637852382891.png

 

Let me know if this is what you're looking for!

 

Greetings,

 

Seb

 

 

AngelosPachis
15 - Aurora

Hi @Degas ,

 

The workflow is flexible to calculate the moving average every any period, depending on which fields you have selected to group by. If you look at the multi-row formula tool and the Running total tool, both are configured to group by year.

 

That was just done to display what's possible with the workflow and how you can do that. If you don't want to group on each year (so the calc doesn't restart then) all you need to do is unselect the tickbox.

 

@Sebastiaandb came up with a neat macro there which you can pick apart and see how to make it work for each one of your cases.

 

Hope this helps,

Angelos

Degas
6 - Meteoroid

@Seb  Thank you very much for taking the time to look into my problem! 

 

Unfortunately, it appears there's an issue with the Alteryx version I'm using cuz I can't open your workflow. I always get the following error message:

 

Degas_0-1637947373852.png

 

I'll try to clarify what's causing this problem.

 

@Angelos thanks again for your reply! I'm sorry, I'm sure it works if you say it does, but even if I remove the group by function, the result is not the one I need. Maybe I'm missing something here.

 

I've managed to solve the problem using the Batch Macro workflow from CharlieS though 🙂 Here's the link to his post in another thread: Link I wonder if CharlieS's solution might the same that Seb suggested, but which I unfortunately couldn't try.

 

 

AngelosPachis
15 - Aurora

@Degas that error message is because Seb is using a more recent version of Alteryx than you do, and that's what causing the error message to pop up. If you click yes that should allow you to open the workflow in your version.

 

If not, you can always open a text editor (notepad for example) and then in the second line change the Alteryx version to the one you are using, and then save it.

 

AngelosPachis_0-1637948037399.png

 

If you try to open the workflow again, it should open with no errors.

Degas
6 - Meteoroid

Thanks! That's a great tip!! 

 

Does the text editor trick always work or is it possible that I run into problems if I do this (for example if a workflow that was created with a newer version uses tools that don't exist in my old version)?

AngelosPachis
15 - Aurora

@Degas it has never happened to me but yes it's a hacky way and not supported officially by Alteryx, although I've seen many people using it. Something to note, if your workflow uses macros, then you should do the same to the macros as well, so everything should be in the same version to play along.

 

Cheers,

Angelos

Degas
6 - Meteoroid

Thanks for the explanation, Angelos!

Labels