Running average for multiple rows at once

[Kay327]

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.

[AbhilashR]

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

Hope this helps.

[ponraj · Accepted solution]

Here is the sample workflow. 

[grossal · Accepted solution]

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

[Kay327]

Thank you @AbhilashR

[jineshnp31]

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

[jineshnp31]

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!