Dynamic average in new column

[serhoshu1118]

How do I create a new column that is the average of the two previous columns that are dynamically updated? 

For example, please see the attached screenshot, On Excel, as each month's data is updated, I want the "MoM" to be the average of the two most recent months. So if I add July-17, the MoM column will become the average of June and July instead of May and June. Is there a way to replicate this on Alteryx? Thanks! 

[mborriero]

The workflow attached should do the trick.

[serhoshu1118]

Hi, thanks! However, the workflow has many errors after I ran it, and the macro doesn't work... would you mind explaining it in words, or share screenshots? 

[mborriero]

That is weird. Try it now, it should work.

1 - transpose = key fields "ID", data field select all excluding ID

2 - formula = I add 01 to the date in order to have 01-Apr-17

3 - Cleanse = transform 01-Apr-17 to 20170401

4 - Sort = by ID and Name

5 - Multi Row = Create new Fielsd "MoM", formula = if [Row+1:ID]!=[ID] then -([Row-1:Value]-[Value])/[Row-1:Value] else null() endif

6-above SELECT = ID, name and VAlue

7-above cross TAb = group data=id, new column headers = Name, new column value=Value and check concatenate.

6-below = filter MoM when it is not NULL

7-below= Select ID and MoM

8 - JOIN = ID=ID and deselect the right_ID

Hope it helps.

[serhoshu1118]

I don't think my version has that specific cleansing macro (see my screenshot). So I tried to change the date column format directly to the yyyyMM01 format - do you think that would work? It is in string type. I tried following your written instructions but the results were weird/full of nulls. 

Is there another way you could show me that example workflow without that macro? 

Thanks so much again! Sorry for the hassle. 

[mborriero · Accepted solution]

Hi @serhoshu1118, if you change the date in 17-Apr-01 it will not work as it will not sort the date in the correct way.

I have replaced the cleanse tool with a DateTime tool. I hope it works now :)

[serhoshu1118]

It seems to be working for me now! thank you so much :) 

[serhoshu1118]

Hi! I have a follow up question - is there a way I could do the same thing, except the average is now between month of this year and month of last year? For example, when I update July 2017's data, the new column's average would be between July 2016 and July 2017. And as each new month's data is updated, the average would also update accordingly. 

This is probably a little more complex than my original question - so any suggestions would help! Thanks so much again. 

[mborriero]

Hi @serhoshu1118, it is actually very similar, I have just changed the MULTI-ROW formula to sum the last 2 months and divide by 2.

[serhoshu1118]

Hi there! Actually my bad - I didn't mean to ask for the average, I meant to ask for the yearly growth in %. For example, in this screenshot, the last column is the difference between July 2017 and July 2016 for each row. And as each month's data is added, for example next month I would put in August's data, the column will dynamically update to the yearly % growth between August 2017 and August 2016. 

Sorry for the confusion - I hope I clarified my problem here! Thanks again.