Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

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

jasonhopkins
5 - Atom

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.

7 REPLIES 7
DultonM
11 - 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!

MarqueeCrew
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
jasonhopkins
5 - Atom

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
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
jasonhopkins
5 - Atom

Absolutely brilliant.

jaipersr
7 - Meteor

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
6 - Meteoroid

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

Labels