This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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!
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)
Alteryx ACE & Top Community Contributor
Chaos reigns within. Repent, reflect and reboot. Order shall return.
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.