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

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.

Moving Average Multiple Fields.yxmd

Macros

Iterative Macro

Accepted answers

MarqueeCrew

@jasonhopkins,

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

Cheers,

Mark

Moving Average Multiple Fields.yxmd

All comments

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

Moving Average Multiple Fields.yxmd

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.

JD_Billigence

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703