Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Calculating Week Number using DateTimeFormat is incorrect

bobpeers
9 - Comet

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.

 

Clipboard01.jpg

thanks,

Bob

24 REPLIES 24
JohnJPS
15 - Aurora

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
9 - Comet

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

JohnJPS
15 - Aurora

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
Alteryx Alumni (Retired)

@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
8 - Asteroid

@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
12 - Quasar

@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
15 - Aurora

Awesome - great catch, @David-Carnes

Thanks!

John

djbridges
5 - Atom

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

Very helpfull! Thank you ACE Emeritus

Labels