Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Create a filter based on a weekday

jmelfreich
5 - Atom

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.

3 REPLIES 3
CharlieS
17 - Castor
17 - Castor

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. 

AndrewBanh
9 - Comet

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

jmelfreich
5 - Atom

Thank you so much!

Labels