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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Assigning Date Ranges a Value

Alteryx Partner

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!

Quasar

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

Alteryx Partner

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!

Quasar

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

Alteryx Partner

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!

 

 

Quasar

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

 

Alteryx Partner

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