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

Thank you so much for this.

 

I did some changes as I work in the ME region, and it worked!!!!

 

 

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

IvanFonseca
7 - Meteor

Hey John,

 

Please, in case I change the day 1 of the week to be Sunday, do I also need to change the formula to be 5 - Switch...?

 

Thank you!

JohnJPS
15 - Aurora

Hi @IvanFonseca,

 

The Switch statement is based on the result from Alteryx's DateTimeFormat function using "%a" ... if you can get that to return 1 and for Sunday, then yes - modify the Switch statement. But I'm guessing that's not necessary as I'm unaware of any settings that would modify the results of DateTimeFormat([date],"a").

 

Hope that helps!

John

 

 

IvanFonseca
7 - Meteor

Hi John,

 

Thank you very much for the quickly response.

 

I said that because I would like that my week starts on Sunday and ends on Saturday, therefore 1=Sunday,2=Monday, etc.

Then, i would have to change the formula that calculates the nearestThursday DateTimeAdd([call_dt],4 - [isoWeekDay], "days") so instead of 4-, it would be 5-, because this formula is based on Monday as the first day of the week.

 

However, I am affraid it could mess up the number of weeks in the year. Could you help me to understand it? Also, I dont understand why we have to calculate this nearest Thursday? I am a beginner, thus any help would be very appreciated!

 

Regards,

Ivan

aalba003
7 - Meteor

Hi Ivan, 

 

Have you tried this one?

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

 

I did that exactly because I needed Sunday to be the 1st day of the week.

 

Regards,

Andressa

IvanFonseca
7 - Meteor

Hi Andressa,

 

Thank you for helping me. 

 

Yes, I have tried but I believe as we consider "Sun",1, "Mon", 2, etc we need to change the formula that calculates the nearest Thursday. Instead 4 - ..., I believe we need to change to 5 - ... I will try to explain below.

 

Before: 

DateTimeFormat(DateTimeAdd([dt],4-Switch(DateTimeFormat([dt],"%a")

 

After:

DateTimeFormat(DateTimeAdd([dt],5-Switch(DateTimeFormat([dt],"%a")

 

Because as far as I know we need always to find the nearest Thursday to calculate the isoWeekYear which is the rest of the formula.

 

Let me know what you think.

 

Cheers,

Ivan

JohnJPS
15 - Aurora

Hi Ivan,

 

I still fail to fully understand the concern.  If you consider '1' to be Monday rather than Sunday (or any other variation), I'm not sure how that impacts anything here. You can still store dates however you want or think of interpret weekday however you want.  But all this is internal to Alteryx and just inside inside an Alteryx formula.  The real question is, if you pass in a date to this formula, does it return the wrong answer for IsoWeek or IsoMonth?  If not, then there's no worry.

 

If your question only has to do with Switch(DateTimeFormat([dt],"%a") returning the wrong number when used by itself (nothing to do with our IsoWeek/Month formulas), then yes: you can add or subtract to it, to make it return what you want it to return, no problem.

 

Hope that helps!

John

 

 

IvanFonseca
7 - Meteor

Hi John,

 

Sorry, maybe I am not explaining me correctly.

 

Please see the pictures below:

 

I have divided your formula in many instances to be easier to understand. 

 

The first picture is with "Mon" = 1 and below I have a formula called NearestThursday which I saw in another forum that they said was necessary to calculate the isoWeekYear to have a correct number of weeks in the year. 

 

IvanFonseca_1-1591379630850.png

 

Now with "Sun"being 1 "Mon" 2 etc. The NearestThursday formula I had to change to 5 - ... to take the Thursday.

 

IvanFonseca_2-1591379679481.png

 

My question is it necessary to change this NearestThursday? Also, what this single formula does? Changing the order of the days in the week (as seen above) will impact the weeks in the year (maybe I could have a day being in the week 52, instead of week 53, etc)?

 

I really appreciate if you take some time to clarify it to me

 

Thank you,

Ivan

 

JohnJPS
15 - Aurora

Hi @IvanFonseca,

 

There are a few things going on in the ISO week number.  First: the ISO standard is Monday = 1 to Sunday = 7, which puts Thursday at 4 which is right in the middle of the range. Also, the ISO week number is based on the "first Thursday" of the year. This means that our formula DateTimeAdd([dt],4 - [isoWeekDay],"days") is perfectly suited for calculating ISO week numbers.

 

If we shift to Sunday = 1 to Saturday = 7, (which I will call "usaWeekday" based on some findings with Google), then Thursday is no longer in the middle of the range... doing DateTimeAdd([dt],5 - [usaWeekDay],"days") will give the wrong day for "nearest Thursday." We really want to continue using "4" since that's the middle of the range... but we also really want to shift the day slightly to make sure we're talking Thursday, not Wednesday. Anyway, the following will give a matching "nearest Thursday": DateTimeAdd([dt],4 - IIF([usaWeekDay]==1,7,[usaWeekDay]-1),"days").

 

My next question is what we're really going for in terms of the final answer.  This website will calculate either ISO year and week numbers, or "USA" year and week numbers. They also use a "Sunday = 1" approach in their calculation, which is by no means universal, as seen here, where it's "Monday" = 1. So the question becomes: are you trying to calculate what is essentially the USA date, or do you still want ISO everything, just a minor adjustment in interpretation of weekday number?  If you want the USA year and week number, we would start over and calculate it somewhat differently. Basically, you may have to do something custom based on your needs, and then test it with a LOT of dates to make sure it gets them all correct.

 

I've attached an updated workflow that calculates everything discussed above:

  • ISO standard
  • ISO standard, but shows the weekday as "Sunday=1"
  • US based on "Sunday = 1"
  • US based on "Monday = 1"

Aside: summary of US approach: "Year" is easy: always calendar year; "WeekNumber" starts at 1 up until the first new Sunday (or Monday), at which point we increment and go every 7 days from there. So, while ISO standard always has 7-day weeks, but has certain dates associated with the "wrong" year, the US approach has truncated weeks at beginning and end of year, but never puts a day outside of its calendar year. Both approaches will frequently have a "week 53".

 

Hope that helps!

John

aalba003
7 - Meteor

Hi @JohnJPS ,

 

That was a great explanation. 

I kept "4" as you mentioned, and it worked.

 

 Thank you again!

 

Andressa Alba

Labels