Version 11 not running version 10 formula

[BarleyCorn]

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

[Balders · Accepted 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 :)

[MarqueeCrew · Accepted solution]

Tom,

Here is a version for you to try.

Cheers,

Mark

[patrick_digan]

@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.