Create a filter based on a weekday

[jmelfreich]

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.

[CharlieS · Accepted solution]

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 · Accepted solution]

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]

Thank you so much!