Hello,
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.
Solved! Go to Solution.
Hi @Kay327, there are multiple ways to achieve averages in Alteryx. I personally prefer using the multi-row formula to compute.
Hope this helps.
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.
Best
Alex
Thank you @AbhilashR
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.
https://support.microsoft.com/en-us/office/trimmean-function-d90c9878-a119-4746-88fa-63d988f511d3
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!
Regards,
Jinesh
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.
https://docs.scipy.org/doc/scipy/reference/generated/scipy.stats.trim_mean.html
And other one posted below was much simpler, faster and stays within Alteryx!