Running average for multiple rows at once

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.

TEST.xlsx

Developer

Developer Tools

Best Practices

Accepted answers

ponraj

Here is the sample workflow.

Running average for multiple rows at once.yxzp

grossal

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

TEST.xlsx

TEST2.xlsx

2020-03-29-running-average.yxmd

All comments

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.

sample.yxmd

ponraj

Here is the sample workflow.

Running average for multiple rows at once.yxzp

grossal

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

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

TEST.xlsx

TEST2.xlsx

2020-03-29-running-average.yxmd

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

trimmean.yxmd

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!

trimmean_solution.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703