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 & year to date

tfahrenk
6 - Meteoroid

Hello!

I'd like to create columns in my spreadsheet that would give me 'month last year', 'month this year'. I have a date column in my data.

 

Tried using formula tool, getting error about not enough parameters for the function.

 

Month this year (attempted, got error): if ([Date] = Max([Date])) THEN [Volume] ELSE "0" ENDIF

Month Last year (have not attempted)

 

Seeing if someone may know how to fix.

 

Thanks

 

Tim

8 REPLIES 8
DanS
9 - Comet

Hey Tim, 

 

Attached a workflow that has 2 formulas in it.  Both of them are using the DateTimeNow() function in order to compare the made up dates I have in the Text Input. 

 

You should be able to adjust that to match on whatever month you were looking to compare to. 

 

Month this Year:

IF DateTimeFormat([Date], "%Y-%m") = DateTimeFormat(DateTimeNow(),"%Y-%m") THEN [Volume] ELSE 0 ENDIF

Month Last Year:

IF DateTimeFormat([Date], "%Y-%m") = DateTimeFormat(DateTimeAdd(DateTimeNow(),-1, "year"), "%Y-%m") THEN [Volume] ELSE 0 ENDIF

 Let me know if this is getting in the right direction. 

 

Thanks,

Dan

tfahrenk
6 - Meteoroid

Hi Dan:

Thanks for your quick reply. I'm pointing this workflow to a flat file & the latest data is from May, so the datetimenow formula would be June. I was seeing if there was a formula that looks at the [date] column for the latest month (for Month this year), then another formula to to look back 12 months (for Month last year).

 

Appreciate your help & patience!

 

Tim

Joe_Mako
12 - Quasar

How about something like the attached?

 

 

tfahrenk
6 - Meteoroid

Hi Joe:

Thanks for the reply. I'm sorry for not being clearer. What I'm looking to do is obtaining the max date in the [Date] field. This would be my "Current Month this year" column. I would then like to compare the same month a year ago ("Current Month last year" column).

 

Using the data file from your example...here would be what the final product would look like (current month = June):

 

CustomerCurrent Month Last YrCurrent Month This Yr
X1830

 

Hopefully that makes sense.

 

Thanks

 

Tim

Joe_Mako
12 - Quasar

How about the attached?

 

MoY.png

 

Notice there there are two Browse tools, depending on if you would like the Max date to be relative to the Customer, or the overall.

tfahrenk
6 - Meteoroid

Hi Joe:

I was unable to open your workflow you sent. Error about the version of Alteryx you created is different than my version (11.0.2.25199).

 

Thanks

 

Tim

Joe_Mako
12 - Quasar
tfahrenk
6 - Meteoroid

Thanks Joe....think this will work!

 

Appreciate your (and DanS's) help!

 

Tim

Labels