Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Average Last 3 Months

FelipeLima
7 - Meteor

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.

6 REPLIES 6
MarqueeCrew
20 - Arcturus
20 - Arcturus

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
danrh
13 - Pulsar

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

image.png

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
7 - Meteor

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
13 - Pulsar

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:

image.png

 

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
7 - Meteor

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
7 - Meteor

groupby multirow.PNG

 

 

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

Labels