community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Calculating the week number

Meteor

Hello,

 

Has anyone had luck on calculating the week number from a date?

 

example: 2016-02-01 is the 6th week of the year

 

Appriciate anyones help with this.

 

Thank you.

Highlighted
Quasar

Is this what you're looking for?

 

In the Formula tool, use the expression DateTimeFormat(<date/time>, "%U")

 

From the Date Time Functions (http://help.alteryx.com/9.5/Reference/DateTimeFunctions.htm)

 

%U

This will return the week number, as 00 – 53, with the beginning of weeks as Sunday.

 

%W

This will return the week number, as 00 – 53, with the beginning of weeks as Monday.

Meteoroid
Hi Talbanese, Find the attached workflow, you can find how we can generate Year, Quarter, Month, Week Number and a Weekday . Thanks, Harsha.
ACE Emeritus
ACE Emeritus

[ removing as errant ]

Meteor

Thanks Phillip,

 

I had tried that however still wasn't populating correctly so I was looking for another way to calculate it.

 

Thank you

Asteroid

Hi all,

 

I can confirm that  DateTimeFormat([Date],"%W") does not return the correct ISO week number (https://en.wikipedia.org/wiki/ISO_week_date)

 

[Date]ISO Week NumberDateTimeFormat ("%W)
Week Number
2017-01-015200
2017-01-020101
2015-12-285352
2015-12-305352

 

The week numbers returned in the accepted solution aren't all ISO standard either (2017-01-01 returned week 2).

 

BUT @JohnJPS posted a single formula that correctly calculates the ISO week number here: https://community.alteryx.com/t5/Data-Preparation-Blending/Calculating-Week-Number-using-DateTimeFor...

 

I'm late to the party, but I'm posting this in case other people come to this thread looking for ISO week numbers. I've attached a workflow which has the test values above and a series of calculations that determine the correct ISO week number (basically @JohnJPS's formula broken down into steps--he has the same steps in his own sample workflow.) 

Atom

Hello All,

 

A little late to this conversation. I believe the ISO weeks were off in 2015 due to an anomaly that causes a 53 week on most fiscal calendars every 6 years.or so. I am struggling with a similar issue being that I do not have a full date for my weeks from previously stored data. The data is in the format of wk/yyyy is it possible to use the %W or %U  Function on this format to make it into weeks?

 

Thanks,

Nick

Asteroid

Hi Nick,

 

Are you trying to get the week number from the wk/yyyy format? Since that will be a String and not a Date data type, the Date/Time functions won't work.  Instead you could use a Test To Columns tool to split up the two parts and then convert each to a number, either with a Select tool or the ToNumber() function--In the attached workflow, I used a Select tool so that I could rename the columns at the same time.

 

Hope this helps,

Jennifer

Labels