Alteryx Designer Desktop Discussions

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

Week start date from Week number and Year

poojitha4
8 - Asteroid

Hi,

 

I have two columns, week number and Year, I need to find the start date of the week(Monday) for that week. Any ideas or leads will be highly appreciated.

Here is a sample

YEARWEEK
201639
201721
201549
20178

 

Thank you,

Poojitha

3 REPLIES 3
danrh
13 - Pulsar

A little bit of a hack solution:

image.png

I create records for the 8 days prior to the week number multiplied by 7, filter down to just Mondays, and grab the biggest date.  You might have to play with the math a little to make sure the week calculations are landing correctly.

 

Hope it helps!

derekbelyea
12 - Quasar

 

Here is a second method that also works and uses a similar approach. 

 

The workflow in Method 2 can be further simplified. Some of the formula fields are included to confirm the logic.

 

2018-02-12_00005.png

cstouwie
7 - Meteor

Hey all, 


Resurrecting this thread....

I have week numbers and year number in separate columns in my data-set and I was needing to convert that directly to the Monday Start Date of that week (ISO Standard). I attempted to use the above solution but found some issues in the fact that it anchors it's calculation on Jan 1. So to try and resolve this I dug in the rabbit hole deep. I'd like to show you the summarized version of that journey of discovery I had:

 

I figured out that the Week Number standard most of us experience is tied to the ISO 8601 date and time standard that assigns each week a number on the Gregorian calendar. There are some years that have 53 weeks and some week 1's start with a previous years date then there is leap years that make things a little more confusing. So needing to know the methodology of how the ISO standard is developing week numbers, I searched and found the ISO standard wiki here which goes through all the possibilities for week 1 vs week 53 in a nice table which is ultimately tied to the day of week that Dec 31 falls on. Here is what it says:

 

"If 31 December is on a Monday or Tuesday it is in week 01 of the next year. If it is on a Wednesday, it is in week 01 of the next year in common years and week 53 in leap years. If it is on a Thursday, it is in week 53 of the year just ending; if on a Friday or Saturday it is in week 52 of the year just ending. If on a Sunday, it is in week 52 of the year just ending in common years and week 01 of the next year in leap years."

 

Pretty simple right? Yeah I didn't think so either. UNTIL searched "Calculating a date given the year week number 'Formula' " and found this website which had an excel solution to this problem:

 

=DATE([YEAR], 1, -2) - WEEKDAY(DATE([YEAR], 1, 3)) + [WEEKNUM] * 7

 

EUREKA! All I had to do was recreate this excel formula in Alteryx.

 

Attached is the rough solution I made myself in Alteryx to do just that.

 

I'm newer to Alteryx so there are probably 20 better ways to do what I did and make it cleaner than it is (sorry for the funky column names). Also, I wish I knew more about building macros since I'm sure this is a great candidate for a macro (Maybe someone wants to do that?!). OR EVEN BETTER YET, Alteryx wants to add this inherently into their DateTime Conversion tool??? (I guy can dream)

 

Hope someone enjoys this....



 

 

Labels