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!
Solved! Go to Solution.
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" )
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: