Average Last 3 Months

FelipeLima

Hello everyone.

I am trying to create a parameter which always give me the average of the last 3 months (including current month).

For example: I have 2 years database.

M1 - 2016;

M2 - 2016;

M3 - 2016;

M4 - 2016;

M11 - 2017;

I basically need to have the average of 3 months.. if today is M11 then (M11+M10+M09)/3 , but it should be dinamically .. if M10 then (M10+M09+M08)/3 if M09 then (M09+M08+M07) and etc..

the problem is.. how can i sum just the 3 months ? and how can i create it dinamically.

I already did it on excel but in alteryx i cant.

Hope someone can help me.

Hugs.

Common Use Cases

Accepted answers

danrh

Sorry, I think I misunderstood the first time. You don't need to see the current average of the last three months, you need to see the average for any given month and the two months prior, is that correct?

This should get you there:

In the Multi-Row Formula tool, you'll need to decide how you want to handle the first two months using the "Values for Rows that don't Exist" dropdown.

Month average.yxmd

All comments

MarqueeCrew

@FelipeLima,

This is one macro that will allow you to calculate the average. Others exist at gallery.alteryx.com and there may be one in the CReW pack for you too at chaosreignswithin.com.

Cheers,

Mark

MovingCalculation.yxmc

danrh

If your dates are in the format you provided, this should give you what you need:

The first Summarize is assuming you have more than one record per month. If you already have it summarized by month, you can remove that one.

I'm also assuming with this that there aren't any future months in the data. If there are, you'll need to tweak the Filter tool to exclude anything after this month.

Hope it helps!

Month average.yxmd

FelipeLima

Hello danrh.

My problem is.. i Always need to show the average of each month.

For example:

M07/2017 - $130

M08/2017 - $150

M09/2017 - $175

M10/2017 - $180

M11/2017 - $190

Then.. the average last 3 months should be

Month Value AVG

M11/2017 $190 $181.66 (which means M11 + m10 + m09/3)

M10/2017 $180 $168.33 (which means M10+m09+m08/3) and so well...

your wf Always show me the avg of the last 3 months.. but not the whole year.. do u think u can help me?

thanksss

danrh

This should get you there:

In the Multi-Row Formula tool, you'll need to decide how you want to handle the first two months using the "Values for Rows that don't Exist" dropdown.

Month average.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335