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

Alteryx designer Discussions

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

Create a filter based on a weekday

Highlighted

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")
) AND
[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()
endif

 

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!

Labels