Version 11 not running version 10 formula

Question

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

CallsPrep7Nov2016V1.yxmd

Balders · Accepted Answer

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 Answer

Tom,

Here is a version for you to try.

Cheers,

Mark