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!
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)))
What syntax error are you getting?
Ahhh...hold on, is that statement in a filter? Assuming it is then you probably need something more like:
_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)
Filter and that worked!!!!
Never thought of an "or" statement.
Thank you soo much!
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.
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.