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 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?
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.
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).
* 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?
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.