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!
Solved! Go to Solution.
Just to double-check, when you pull the report on Monday you want data for Friday, Saturday and Sunday only?
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
Correct, and all other weekdays I would only need the previous day's submissions.
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
@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()
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
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!