Hi,
In my old version of alteryx I had the following DateTime in a formula tool to create a new column with Week Commencing based on a column of dates:
Week0Comm = datetimeadd(tostring([Year],0)+'-01-01',1 - tonumber(datetimeformat(tostring([Year]),0)+'-01-01', "%w")),"days")
Followed by:
WeekComm = datetimeadd([Week0Comm], tonumber(week)*7,"days")
And output in old version gave me WeeKComm with the correct week commencing for the date in the data.
The error message in the new version is centered on the '-01-01'
Old workflow is attached. Help Please!!
Solved! Go to Solution.
Your error seems to be focused around the DateTimeAdd in Week0Comm. I'm not sure what changed between version 10 and version 11 but I think the first part of the DateTimeAdd needs to explicitly be a date. If you wrap a date time parse around [Year1]+'-01-01' then the formula works. In full this is the formula that I got to work.
datetimeadd(DateTimeParse([Year1]+'-01-01',"%Y-%m-%d"),1 - tonumber(datetimeformat(tostring([Year1],0)+'-01-01',"%w")),"days")
I hope this helps :)
Tom,
Here is a version for you to try.
Cheers,
Mark
@BarleyCorn I just ran into the same issue as you where I was creating a date with strings in 10.0 but my formula didn't work in 11.0. It seems as though they've fixed it in 11.3.