Different Filtering for Different Day of the Week

[jessimica602]

Hey Everyone,

I am trying to create a delivered report that sends different data based on the day of the week. It will send Monday, Wednesday and Friday. For Wednesday and Friday I want it to look back on the past two days (Wednesday - it'll show Monday and Tuesday data; Friday - it'll show Wednesday and Thursday data) but for Monday I want it to look back three days (shows Friday, Saturday and Sunday data).
I've tried a few different ways but I think below is correct but I keep getting a Syntax error near "between" and can't seem to figure out why... It's easy I'm sure but I just don't see it.

IF(_day_of_week (current_date,1) = 1)
Then ([EjhDateTimeCreated] between _add_days (current_date,-3) and _add_days (current_date,-1))
ELSE ([EjhDateTimeCreated] between _add_days (current_date,-2) and _add_days (current_date,-1))

Any help would be greatly appreciated.

Thank you!

[jessimica602]

Also tried the below and it didn't work...

IF(_day_of_week (current_date,1) = 1)
Then ([EjhDateTimeCreated] between (_add_days (current_date,-3)) and (_add_days (current_date,-1)))
ELSE ([EjhDateTimeCreated] between( _add_days (current_date,-2)) and (_add_days (current_date,-1)))

[BigChris]

What syntax error are you getting?

[BigChris]

Ahhh...hold on, is that statement in a filter? Assuming it is then you probably need something more like:

Code Select Expand


_day_of_week(current_date,1) = 1 and [EjhDateTimeCreated] between _add_days(current_date,-3) and _add_days(current_date,-1)
or
_day_of_week(current_date,1) <> 1 and [EjhDateTimeCreated] between _add_days(current_date,-2) and _add_days(current_date,-1)

[jessimica602]

Filter and that worked!!!!
Never thought of an "or" statement.

Thank you soo much!

[BigChris]

My pleasure - essentially, you can't use an if statement the way that you wanted to in a filter...you need to have something that equates to either true or false.

[srmoure]

Alternatively you can create a data item with the IF statement and the create a filter based on the item. It's more efficient to do it directly on the filter.