Calling all Racers for the Alteryx Grand Prix! It's time to rev your engines and race to the stage at Inspire! Sign up here.

Alteryx Designer Discussions

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

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.

15 - Aurora
15 - Aurora

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!