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

Month over month comparison

annzhu
7 - Meteor

Hi.

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

 

Say My dataset look like below: 

Capture.PNG

5 REPLIES 5
annzhu
7 - Meteor

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

andyuttley
11 - Bolide
11 - Bolide

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:

MoM.PNG

 

 

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

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
11 - Bolide
11 - Bolide

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

MoM2.PNG

 

 

Hope that helps!

Andy

annzhu
7 - Meteor

Hi Andy

Works like a charm! Many thanks!

Labels