Dynamically Calculate L3M Average

[mercurial_maverick]

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. 

[christine_assaad · Accepted solution]

HI @mercurial_maverick 

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

[ggruccio]

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)

Which produces this output:

[ggruccio]

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]

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

[ggruccio · Accepted solution]

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

[mercurial_maverick]

@ggruccio  Thank you!

[mercurial_maverick]

@christine_assaad Thank you!