This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I'm using the formula DateTimeFormat([date],"%W") to calculate week number but it seems that the results are incorrect for the end 2016 and start 2017.
For example the Date 01-01-2017 was a Sunday so should be in week 52 but Alteryx shows this as being in week 00 with week 01 starting on 02-01-2017 (in dd-mm-yyyy format). Does Alteryx do this because it doesn't allow a week number to cross years? To make it correct I have to manually alter week 00 to be week 52 which feels like a hack.
Since it can be written as a single expression, it's also easy to create am XML Formula AddIn for it, which I've attached here. If you copy this XML file to Program Files\Alteryx\bin\RuntimeData\FormulaAddin, (and restart Alteryx), then you will see "IsoYear" and "IsoMonth" in the formula tool under the DateTime section, making for quick, convenient usage.
Amazing thread - thanks guys. Not wanting to be a pain, but what about doing the reverse e.g. I have some dates that are Weekly e.g. 2017W39 and want to convert them to a start date. Have been playing around with the above, but not having much luck...