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

[Suraj855]

Hi John,

Can I get XML for the month as well.

Regards,

Suraj

[JohnJPS]

@Suraj855, please see the response from @David-Carnes earlier in this the discussion.  He's already done the Month. 🙂

[TomasKozlik]

Hello,

would this work on Alteryx server if I use this XML workaround?

Thanks

[David-Carnes]

@TomasKozlik 

Yes.
You'll need to check that it's still there after upgrading.

Best,

David

[FabioP]

Dear All,

I'd like to use this logic for 'Financial Year' where for example the first date of year is 2022-04-01.

Is it possible to change any part of this formula to start to count from April?

Ex.:

01/04/2022	52
02/04/2022	52
03/04/2022	52
04/04/2022	01
05/04/2022	01
06/04/2022	01
07/04/2022	01
08/04/2022	01
09/04/2022	01
10/04/2022	01
11/04/2022	02
12/04/2022	02
13/04/2022	02
14/04/2022	02
15/04/2022	02