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

Alteryx designer Discussions

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

Assigning Date Ranges a Value

Asteroid

Hello - I'm looking to group line items based off of range of dates.  Specifically, I have one Date column (format '3/18/2019') and would like to create a new column with week values based off of specific ranges in the Date column.  The new column's dimensions would follow:

 

  • 3/4 - 3/10 = "Week 1"
  • 3/11 - 3/17 = "Week 2"
  • 3/18 - 3/24 = "Week 3"
  • 3/25 - 3/31 = Week 4"
  • and so on through "Week 12"

Thank you!

Pulsar
Pulsar

Hi @navypoint16 ,

 

You can try this formula on your date column (create a new column called "Week" and data type is Int16):

tonumber(DateTimeFormat(DateTimeParse([Date],"%m/%d/%Y"),"%W"))-8

 

- The DateTimeParse function converts the date into date format

- The DateTimeFormat function then converts that date into the week of the whole year (%W specifies that a week starts on Monday)

- Tonumber converts the string of the week number into an actual number

- Since March 4th is in the 9th week of the year, we subtract 8 to get 1

 

 

This should work well into the future, past the initial 12 weeks. Let me know if this solves your challenge.

 

Cheers!

Esther

Asteroid

Hi Esther,

 

Thanks - this is very helpful.  When I created a new column with data type Int16, however, the result was "-8" for every row of data.  Do you know why this might be happening?

 

Thanks!

Pulsar
Pulsar

Hi @navypoint16 

 

Would you please copy and paste the formula you're using so I can troubleshoot? My guess is something off in the DateTimeFormat and DateTimeParse

 

Cheers!

Esther

Asteroid

Yes - please see below:

 

tonumber(DateTimeFormat(DateTimeParse([Target / Actual End],"%m/%d/%Y"),"%W"))-8

 

Is this enough to help troubleshoot?  The new field data type is Int16 and the incoming date field (Target / Actual End) is Date.

 

Thanks!

 

 

Pulsar
Pulsar

Hi @navypoint16 

 

If the incoming field is already a date, then you don't need the DateTimeParse. Your post showed a format that Alteryx wouldn't understand to be a date ;)

Try this instead:

tonumber(DateTimeFormat([Target / Actual End],"%W"))-8

 

Cheers!

Esther

 

Asteroid

Sorry, Esther! One more question..

 

I had a date in 2020 (2/1/2020) that was assigned a value of -4.  The formula does not seem to take into account the year.  Can you think of any workarounds to account for this?  The value should be 50, rather than -4.

 

Thank you!

Labels