Calculate Alteryx-date based on Week & Year fields (YYYY_WW)

[erike67]

Hi all, 

I'm trying to create a Alteryx date (so that I can do time series analysis) based on the date field in my source data. My source data has dates in the format YYYYWW (year-week, e.g. 201630), but no date or month information. I have done plenty of research online without finding any information about how to create an "Alteryx-date" based on data in this format. 

Does anyone in the community know how to do this? Or is it not possible to create a date field using year-week source data? 

Thanks! 

[JohnJPS]

Hi @erike67

Can you create a date YYYY-01-01, then DateTimeAdd WW*7 days in order to arrive at something reasonable?  e.g.:

DateTimeAdd(
   DateTimeParse(ToString(FLOOR([yyyyww] / 100))+"0101","%Y%m%d"),
   7*(MOD([yyyyww],100)-1),"days"
)

[Federica_FF · Accepted solution]

Here is another way to get the same result:

DateTimeAdd(left([YYYYWW], 4)+"-01-01", (tonumber(right([YYYYWW], 2))-1)*7 , "days")

 Assuming the string YYYYWW is always 6 characters, (even the first 9 weeks) and assuming you're talking about calendar week and not fiscal week. Fiscal weeks barely starts from 01/01, they usually starts with the 1st monday of the year.

Otherwise you'll need a to add one zero if the lenght is 5 and not six and you have to cancatenate the real first fiscal day instead of 01-01: