Alteryx Designer Discussions

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

Running average for multiple rows at once

6 - Meteoroid



I am new to the community and have been trying to create a running average for multiple rows at once. I want an average of the last 10 days. I can easily create the results an Excel but I have not been able to duplicate the process in Alteryx. Do you have any ideas of how this can be done?


Thank you.

14 - Magnetar
14 - Magnetar

Hi @Kay327, there are multiple ways to achieve averages in Alteryx. I personally prefer using the multi-row formula to compute.





Hope this helps.

13 - Pulsar

Here is the sample workflow. 


15 - Aurora
15 - Aurora

Hi @Kay327,


here is another way to do it:



I wanted to build a more dynamic workflow than one of the previous comments, but avoid using macros. The workflow will build running averages for all columns in the file. 


What happens:

1) Columns to rows (Transpose

2) Sorting them correctly (Sort)

3) Building the Avaerage (Multi-Row Formula)

4) Building a Counter per Category (Multi-Row Formula)

5) Replacing the first 9 rows with nulls (Formula)

6) Removing the counter (Select)

7,8,9 Shifting normal columns and average back (+ Formula for correct naming for the averages) (2x Cross Tab, 1x Formula)

10) Join all together


I have tested it using your file and an extended file with 7 different columns and building the average for all of them. There is no need for changing something in the workflow. You could run this with 100+ columns and will get a running average for all of them.


Your file output:



Second test file output:



I'll attach the workflow as well as the sample files (I removed all hard coded average columns in your file).


Let me know what you think.






6 - Meteoroid

Thank you @AbhilashR


8 - Asteroid

Hi @AbhilashR 


Thanks for posting the solution. I have an additional requirement on top of the workflow, if you could help take a look!


I need to calculate TRIMMEAN similar to how excel does it.


The case is,

* Calculate the average of the upper 12 and lower 12 values

* Exclude 20% data points off the range (takes off the max 2 records and min 2 records from the range and calculates the average after)


I was able to calculate the first part based on your solution and similarly get a top max and top min, looking to solve for the next step. How can we get a second max and second min to exclude before getting an average from the range?


Attached is a sample workflow.




Thank you!




8 - Asteroid

I was able to solve this. Took some playing around, but was able to come up with two solutions.. One using Python scipy package where I built an array uaing the method posted by @AbhilashR  and passed the list to a python script.


And other one posted below was much simpler, faster and stays within Alteryx!