community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

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

Highlighted

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.

Bolide

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!

Alteryx Certified Partner
Alteryx Certified Partner

@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.

 

Capture.PNG

 

Capture.PNG

 

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

 

Cheers,

Mark

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.

Alteryx Certified Partner
Alteryx Certified Partner

@jasonhopkins,

 

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

 

Capture.PNG

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.

Absolutely brilliant.

Labels