cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Column with a specific date

SOLVED
Highlighted
Meteor

Hi everyone, 

 

I'm trying to convert an data file that's normally made using multiple Excel sheets to be created via Alteryx. One of the columns in the Excel sheet is a "Week of" field that indicates the Sunday of the week that the data is covered since we look at the data week by week. Normally, the aggregation of the data happens about 10 days after that starting Sunday (i.e. 2/11/2018 is a Sunday so when it says 'week of 2/11/2018' it'll cover 2/11-2/17, but the data for that week wouldn't be pulled until 2/21/2018). Because it happens later, the use of something like a DATETIMENOW or something formula isn't possible. 

 

Is there a formula or a way to add the value of that Sunday? Right now, I'm just adding a formula and writing the date I want, but wasn't sure if there was a better way to do this

 

Thanks!

@MKM

 

You'll need to add a formula tool with the following expression.  Just swap out the [Date] for whatever your date field is.

 

DateTimeAdd([Date],
	Switch(DateTimeFormat([Date],'%a'),0,
		'Mon',-1,
		'Tue',-2,
		'Wed',-3,
		'Thu',-4,
		'Fri',-5,
		'Sat',-6),
	"days")