Calculating Week Number using DateTimeFormat is incorrect

[bobpeers]

Hi,

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.

thanks,

Bob

[JohnJPS · Accepted solution]

Interesting, and there appear to be quite a few dates where it does not match the ISO standard... there's some good commentary here, including the note that ISO can differ from the actual year as well... http://stackoverflow.com/questions/35010785/iso-week-number-in-vbscript-or-vba

Based on that I created the attached workflow, which duplicates what they have, in an Alteryx formula... I've verified that the test rows in there match with SQL Server's ISOWW DatePart.

I have several expressions in the Formula (in the workflow)... the last is as a single expression that can be used stand-alone (for a variable [dt])...

PadLeft(ToString(FLOOR(DateTimeDiff(DateTimeAdd([dt],4-Switch(DateTimeFormat([dt],"%a"),7,"Mon",1,"Tue",2,"Wed",3,"Thu",4,"Fri",5,"Sat",6),"days"),DateTimeParse(ToString(ToNumber(DateTimeFormat(DateTimeAdd([dt],4-Switch(DateTimeFormat([dt],"%a"),7,"Mon",1,"Tue",2,"Wed",3,"Thu",4,"Fri",5,"Sat",6),"days"),"%Y")))+'-01-01',"%Y-%m-%d"),"days")/7)+1),2,"0")

[bobpeers]

Thanks, I'll test when I'm back from holiday but that seems like quite a workaround 😉

[JohnJPS]

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.

[RodL]

@JohnJPS,

Thanks for the reminder that you can add custom functions this way. Between you and @MarqueeCrew, I'm getting a good refresher on a couple of forgotten items. :-)

[jlefeaux]

@John JPS:  I love the add-in...  I did not know you could do this with Alteryx (I'm relatively new to the tool).  Definitely provides an incentive to learn XML!

Since I don't know XML -- if you happened to have the opportunity and the inclination to add an IsoWeek function in your add-in.... Well, that would be spectacular :)

[David-Carnes]

@jlefeaux @JohnJPS

The IsoDates.xml has formulas for IsoYear and IsoMonth, but the IsoMonth is actually returning the ISO Week value,

I have taken the liberty of correcting JohnJPS's formula XML in the attached.

By the way @JohnJPS, thank you very much for your posts.

Ciao,

David

[JohnJPS]

Awesome - great catch, @David-Carnes

Thanks!

John

[djbridges]

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

I saw this thread https://community.alteryx.com/t5/Data-Preparation-Blending/Exact-week-number-conversion-to-date/td-p..., but really like having a saved function that I can call rather than rewriting complex formulas each time....

[Crete]

Very helpfull! Thank you ACE Emeritus