Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Running average for multiple rows at once

Kay327
6 - Meteoroid

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.

6 REPLIES 6
AbhilashR
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.

AbhilashR_0-1585443682778.png

 

  

AbhilashR_1-1585443713949.png

Hope this helps.

ponraj
13 - Pulsar

Here is the sample workflow. 

workflow2.PNGworkflow.PNG

grossal
15 - Aurora
15 - Aurora

Hi @Kay327,

 

here is another way to do it:

 

grossal_0-1585478648060.png

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:

grossal_1-1585479022392.png

 

Second test file output:

grossal_2-1585479043580.png

 

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
6 - Meteoroid

Thank you @AbhilashR

 

jineshnp31
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.

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.

 

jineshnp31_0-1633460047434.png

 

Thank you!

 

Regards,

Jinesh

jineshnp31
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. 

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! 

jineshnp31_0-1633644262119.png

 

Labels