Filter IF statement to return three days if today is monday?

[Mike-T]

I have a workflow that Tuesday through Friday I need to pull the previous day's data from a database, but on Monday I need to pull three day's worth (Friday, Saturday, and Sunday).

Is there something I can add to the below IF statement to also return Saturday and Sunday? or am I taking the wrong approach?

As is, it is giving me Friday's data if today is Monday, and just prior day for the rest of the week(so mostly working).

IF DateTimeFormat(DateTimeToday(), "%a") = "mon"
THEN DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d") = DateTimeFormat(DateTimeAdd(DateTimeToday(), -3, "days"), "%Y-%m-%d")
ELSE DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d") = DateTimeFormat(DateTimeAdd(DateTimeToday(), -1, "days"), "%Y-%m-%d")
ENDIF

Thanks

[patrick_digan · Accepted solution]

@Mike-T Here is the formula I would use:

DateTimeDiff(DateTimeToday(),DateTimeTrim([SCRIPT_RUN_DATE],"days"),"days") <(IIF( DateTimeFormat(DateTimeToday(), "%a") = "mon",4,2))

That will take the difference between today and script run date (in whole days) and then grab either the previous day (<2) or the previous three days (<4) depending on the day of the week. I've taken the liberty of completely changing around your formula. If you wanted something closer to what you currently have, you could just change the then statement as shown below to use the datetime diff:

IF DateTimeFormat(DateTimeToday(), "%a") = "mon"
THEN DateTimeDiff(DateTimeFormat(DateTimeToday(), "%Y-%m-%d"),DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d"),"days")<4
ELSE DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d") = DateTimeFormat(DateTimeAdd(DateTimeToday(), -1, "days"), "%Y-%m-%d")
ENDIF

[Rohit_Bajaj]

Hi Mike,

Can you try creating a formula field  Monday = IF DateTimeFormat(DateTimeToday(), "%a") = "mon" then 'Y' else 'N'?

In filter check if 

(Monday = Y AND 

DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d") >= DateTimeFormat(DateTimeAdd(DateTimeToday(), -3, "days"), "%Y-%m-%d") && DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d") <= DateTimeFormat(DateTimeAdd(DateTimeToday(), -1, "days"), "%Y-%m-%d") OR

(MONDAY=N AND

DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d") = DateTimeFormat(DateTimeAdd(DateTimeToday(), -1, "days"), "%Y-%m-%d"))

Please see if this can get you started.

Thanks,

Rohit Bajaj

[Mike-T]

---

@patrick_digan wrote:

@Mike-T Here is the formula I would use:

DateTimeDiff(DateTimeToday(),DateTimeTrim([SCRIPT_RUN_DATE],"days"),"days") <(IIF( DateTimeFormat(DateTimeToday(), "%a") = "mon",4,2))

That will take the difference between today and script run date (in whole days) and then grab either the previous day (<2) or the previous three days (<4) depending on the day of the week. I've taken the liberty of completely changing around your formula. If you wanted something closer to what you currently have, you could just change the then statement as shown below to use the datetime diff:

IF DateTimeFormat(DateTimeToday(), "%a") = "mon"
THEN DateTimeDiff(DateTimeFormat(DateTimeToday(), "%Y-%m-%d"),DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d"),"days")<4
ELSE DateTimeFormat([SCRIPT_RUN_DATE],"%Y-%m-%d") = DateTimeFormat(DateTimeAdd(DateTimeToday(), -1, "days"), "%Y-%m-%d")
ENDIF

---

@patrick_digan
Thank you I think this is getting me on the right track, the only problem I am having is it is returning today's date along with the last 3 (I changed "mon" to "tue" for testing purposes. but I think I can figure this out using the DateTimeDiff function.