Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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
14 - Magnetar
14 - Magnetar

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