How to create a moving average without using the multi-row tool for each field?

[jasonhopkins]

I was wondering if someone could point me in the right direction regarding the question I have above.  I am pretty new to Alteryx, as in, I have been using it for maybe three days now.  My role in the company is a system admin, so I am usually setting servers and applications up and seldomly using them.  Alteryx is an exception as I find the tool to be pretty amazing and attending Inspire a few weeks ago has me pretty amped up right now.  In the past three days I have already used it to create some Tableau extracts that I would have had no idea how to do using Excel.

Now for my problem.  I have been asked to find a way to create a running average for multiple columns worth of data.  As a test, I have built out 5 columns, each with maybe 20 rows of numbers.  I can use the multi-row formula tool but that only works for one field at a time.  When I research this tool, it specifically has a note that you need one of these tools for every field.  For my test, this is no biggie, but this doesn't work for production as we need to create a moving average on more than 5000 columns of data.  Could you imagine doing that by hand?

I did get my hands on a moving summarize macro that's much better than doing it by hand but copying and pasting 5000 of those in one work flow is still less than enticing.

I am going to include my work flow using the macro and I was hoping someone could tell me a quicker way to make this work with 5000 columns.

Thanks for any insight you might be able to offer.

[DultonM]

Hi Jason!

I think a macro can get you where you need to go. You can create a list of all the columns in your data and the macro would loop through each column name and adjust the configuration of the summarize macro or multi-row tool appropriately for that column. Unfortunately, when I download your sample workflow, the macros do not work because the workflow can no longer find them. Could you please attach this "moving summarize" macro you used? Once I see what it is doing, I'll know if this idea will work. Thanks!

[MarqueeCrew]

@jasonhopkins et al,

Here is an approach:  Group the data by transposing it into name and value pairs.  Then you can create a single input to the internal function of moving averages (i used my macro for this).  The output is then calculated for every column of data within a single pass of your data.

Hopefully, this will get you going..... (with the right results)

Cheers,

Mark

[jasonhopkins]

Thanks Mark.  I am trying to wrap my head around what you created which looks to do what we need.  Is this doing a running average of 2 rows or 5?  Is the Expression in Multi-Row Formula where I can control how many rows should be averaged together?

Also, is it possible to split the single column with everything into a a column for each x(value)?  I am wondering if you can make the browse it look more like it did in my original?

Again, I appreciate what you have done and so quickly too.

[MarqueeCrew · Accepted solution]

@jasonhopkins,

This is working on EVERY column of data that you have.  Attached is an updated workflow that creates the following data:

Cheers,

Mark

[jasonhopkins]

Absolutely brilliant.

[jaipersr]

Hi,

I downloaded your file and went through it. It seems as though you have a 10 day moving average as opposed to a 30 day one which is written in your example. Am I wrong here?

[JD_Billigence]

I did notice that too. It's just a question of updating the dataset to 30-day periods.