Dynamically Calculate L3M Average

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.

Example.xlsx

Datasets

Developer Tools

Reporting

Dynamic Processing

Accepted answers

Christine_Assaad

HI @mercurial_maverick

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

Dynamically Calculate L3M Average.yxmd

ggruccio

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

last3monthsaverage_non_null.yxmd

All comments

Christine_Assaad

HI @mercurial_maverick

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

Dynamically Calculate L3M Average.yxmd

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:

last3monthsaverage_non_null.yxzp

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

@christine_assaad Thank you!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703