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.

[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

[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!

[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 · Accepted solution]

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.

[FelipeLima]

Hi Dan... thanks for the reply. it worked really well.

let me ask just one more question.. let´s say that i wanna add another dimension, for example:

M07/2017- CRF          - $130

M07/2017- OTHERS  - $110

M08/2017- CRF          - $150

M08/2017- OTHERS  - $105

M09/2017- OTHERS  - $150

M09/2017- CRF          - $165

M10/2017- CRF          - $180

M10/2017- OTHERS  - $180

M11/2017- OTHERS   -$190

M11/2017- CRF          -$190 

Then.. the average last 3 months should be

Month       Value  ACCOUNT      AVG

M11/2017  $190   CRF               $181.66 (which means M11 + m10 + m09/3 - but - only the values that represents the account CRF)

M11/2017  $190   OTHERS       $173.33 (which means M11 + m10 + m09/3 - but - only the values that represents the account OTHERS)

M10/2017  $180   CRF               $168.33 (which means M10+m09+m08/3)

M10/2017  $180   OTHERS       and so.............

based on your logical it won't work because your multiple row is configured to work with the dates, am i right? are there any way that i can add another dimension and just do the average for each account?

thanks again.

[FelipeLima]

Well.. i already find out. hahaha .. thanks man. have a good one.