Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Create a filter based on a weekday


Hi Everyone,


I am creating a filter that I need to remove last 4 weeks of data. I need it to self updating, so up until now I have used a formula like this:


[Date] >= ToDate(
DateTimeAdd(DateTimeToday(), -4 * 7, "days")
[Date] <= DateTimeToday()


However this requires me to run the work flow on the same day each week, (e.i. every Monday), This is challenging to do, and if I forget, then I need to manually adjust the date filter to ensure that I get all data removed correctly. So using the current filter if I ran this on Tuesday instead of Monday, four weeks of data from Tuesday are going to be removed.


Does anyone know a function that is both dynamic and tied to a given day of the week? So remove last 4 weeks from the most recent Monday, so even if I ran it on Tuesday it will remove data from 4 weeks from the past Monday instead of Tuesday.

Alteryx Certified Partner
Alteryx Certified Partner

The date time formats of %A and %a represent 'Monday' and 'Mon' respectively. to determine the current day of the week, you could use DateTimeFormat(DateTimeNow(),'%a') which would result in 'Thu' today. This can be used to construct an if statement to make the adjustments you need. 

Alteryx Partner

Hi @jmelfreich 


I have created a workflow (attached below) for you which lets you choose which day of the week you want to find the last 4 weeks from.


To start, the first formula tool flags if you are currently in the current week and finds the Day of the week as mentioned by @CharlieS.


The 2nd formula tool uses the formula:


if [Day] = 'Mon' and [IsCurrentWeek] = 1
then DateTimeAdd([date],-28,'days')
else Null()


If you want to change the day of the week, just change the bit that is italicised. 


To get that 4 week date applied across the other records, I used a multi-row formula to achieve this.


Finally I used the filter tool to remove the dates within the last 4 weeks.


Hope this helps you!


- Andrew

Thank you so much!