Month over Month & year to date

[tfahrenk]

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

[DanS]

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]

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]

How about something like the attached?

[tfahrenk]

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):

Customer	Current Month Last Yr	Current Month This Yr
X	18	30

Hopefully that makes sense.

Thanks

Tim

[Joe_Mako]

How about the attached?

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]

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 · Accepted solution]

Please try the attached. 

For future reference, https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Adjusting-Alteryx-Files-for-Different-Versio...

[tfahrenk · Accepted solution]

Thanks Joe....think this will work!

Appreciate your (and DanS's) help!

Tim