Month over Month & year to date

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

Date Time

Accepted answers

Joe_Mako

Please try the attached.

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

MoY Last only.yxmd

tfahrenk

Thanks Joe....think this will work!

Appreciate your (and DanS's) help!

Tim

All comments

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

Month_TY_LY.yxmd

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?

MoM and MoY.yxmd

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.

MoY Last only.yxmd

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

Please try the attached.

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

MoY Last only.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703