We are currently experiencing an issue with Email verification at this time and working towards a solution. Should you encounter this issue, please click on the "Send Verification Button" a second time and the request should go through. If the issue still persists for you, please email support@alteryx.com for assistance.

Alteryx Designer Discussions

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

Dynamically Calculate L3M Average

mercurial_maverick
8 - Asteroid

Hello all,

 

Please see attached file which is an output of a workflow I've. This is a rolling 12 month data that gets updated regularly. I want to calculate the L3M Average based on below cases.

 

* If L3M have non zero values (eg: row 2) - calculate Avg of L3M (Oct, No,Dec)

* If L3M have zero values (eg: row 4) - calculate Avg of previous 3 months that is July, August, September. 

 

 

7 REPLIES 7

HI @mercurial_maverick 

 

Please test if the below is what you are looking for. Thanks!

 

christine_assaad_0-1611348797058.png

 

ggruccio
13 - Pulsar
13 - Pulsar

Hi @mercurial_maverick ,

 

Will something like this work for you?  It basically finds the three most recent non-null values and averages them.  I transposed the data and filtered out the nulls...then used a running total tool on the terminal value to get the highest value of each remaining month....sorted in descending order then sampled the top 3 and computed averages.  I also concatenated the months used in the average so you could audit it to make sure it was grabbing the correct months.  (workflow attached)

 

ggruccio_1-1611348634507.png

 

 

Which produces this output:

 

ggruccio_0-1611348607823.png

 

ggruccio
13 - Pulsar
13 - Pulsar

Sorry I did not know this was being worked on.  Please use the solution provided by @christine_assaad if it works better.  Thanks!

mercurial_maverick
8 - Asteroid

@ggruccio Could you please share only the workflow alone and not as a package? Import results in error.

ggruccio
13 - Pulsar
13 - Pulsar

Hi @mercurial_maverick , please see the attached without the packaged workflow.

mercurial_maverick
8 - Asteroid

@ggruccio  Thank you!

mercurial_maverick
8 - Asteroid

@christine_assaad Thank you!

Labels