Month over month comparison

[annzhu]

Hi.

How could I add a column for Month over month variance? Thanks!

Say My dataset look like below: 

[annzhu]

Any what if I want to pick up any 2 months for comparison? To create a dropbox to pick up two months? How?

[andyuttley · Accepted solution]

Hi @annzhu 

Yes both are doable, in fact there are lots of ways you could go about this. My method based on your current table structure would be to transpose and use a multirow formula grouped by your account. 

There's two questions from your two messages, so I've included both in the example attached:

There's a container for each question. Note I've assumed variance as ([Value]/[Row-1:Value])-1, but you can just do a simple ([Value]-[Row-1:Value]) if that's what you're after

Question 2 has an extra filter after the transpose; this is where you choose the months you'd want to compare (as opposed to Q1 which always looks back 1 month). Right now, Q2 this is set to look back 2 months within that filter, but you could use an action tool with some on the filter if you wanted a user to click two boxes to choose the months to compare. I don't know if you're new to those tools; there's some useful info here https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-Action/ta-p/35500 , or obviously you can simply update the filter yourself if that's easier. 

For future reference, another option for future reference would be to turn your headers into actual dates (using 'datetimeparse'), and use date formulas from there. 

Let me know if this works for you, or needs more explaining. Hope it helps! 

Andy 

[annzhu]

Thank you Andy, both worked perfect!

But what I need is more than that:

Say I have 12 columns(Jan to Dec 2019) and I need the 13th column for MOM (Current M-Previous M).

How could I get it automatically?

Say today is Jun 30, then MOM=Jun month- May month

Tomorrow will be July 1st, then the MOM will change automatically to Jul month- Jun month.

Thanks again for your help on this issue!

[andyuttley]

No problem @annzhu - pleased it worked!

Yes, you can add that in too; you just need a Date Time Now tool (which will timestamp whenever you hit run) and the logic that turns it into the format of your column headers:

- DateTimeFormat([DateTimeNow],"%Y%b") and 

- DateTimeFormat(
DateTimeAdd([DateTimeNow],-1,'month'),"%Y%b")

...will give you 2019Jun and 2019May (if you ran it during June)

From there it's just a filter to match to the result of your formulas as attached

Hope that helps!

Andy

[annzhu]

Hi Andy

Works like a charm! Many thanks!