Filter results by date with day of week conditions

[mike_j1028]

Hi, I pull a report from a third party each weekday that is a living list of all products that have ever been submitted to them. I need to filter out results to get only the submissions through the previous business day, which must include Friday through Sunday when I pull the report on Monday. After filtering for [Today]="Monday", I have tried multiple filters and DateTime formulas to get the needed output (if T, three previous days, if F previous day only) but have not made any progress. How can I remove all rows but those that meet the filter criteria?

Thank you!

[MichalM]

@mike_j1028 

Just to double-check, when you pull the report on Monday you want data for Friday, Saturday and Sunday only?

[OllieClarke]

Hi @mike_j1028  The following formula will create a cutoff date for use in a filter

---

If Datetimeformat(DateTimeToday(),'%A')='Monday' THEN DateTimeAdd(datetimetoday(),-3,'day')
ELSE datetimeadd(Datetimetoday(),-1,'day')
ENDIF

---

You can then use [Date]>=[Cutoff Date] in a filter tool

Hope that helps, Ollie

[mike_j1028]

Correct, and all other weekdays I would only need the previous day's submissions. 

[MichalM]

@mike_j1028 

In which case the below should work

If DateTimeFormat(DateTimeNow(),"%A") = "Monday" 
THEN [Date] >= DateTimeAdd([Date], -3,"days") AND [Date] <= DateTimeAdd([Date], -1,"days")
ELSE [Date] = DateTimeAdd([Date], -1,"days")
ENDIF

 

[OllieClarke]

@mike_j1028 if you don't want to include today's data then update the filter I mentioned above to:

[Date]>=[cutoff date] 

AND

[Date]<datetimetoday()

[Jonathan-Sherman · Accepted solution]

Hi @mike_j1028,

I think this solves the issue you're having.

Input:

Output: Using the DateTimeToday function (top filter tool)

Output: Using monday to prove 3 day backlog (bottom filter tool)

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed, the bottom filter can be removed in your case.

Regards,

Jonathan

[mike_j1028]

Thanks to everyone who helped me work this out. I couldn't get most of the solutions to give me the proper output. Most likely it was a formatting issue with the input file. Below is what I ended up getting to work based off of Jonathan's suggestion. Hopefully this will work on Monday as well. Thanks again!