Hello,
I am currently working on a report in which I need to use an if statement in a date field. I am trying to get the Date field to automatically update based on the current day of the week. I need the Date field to return the last four weeks of information. Essentially, I need the field to work something like this:
if(_day of week (current_date)) = 1
Then [Date] between _add_days (current_date, -20) and _add_days (current_date, -2)
Else if(_day of week (current_date)) = 2 etc...
For some reason when I try to run the field like this I keep getting errors like ccl exception and parsing error. I am not quite sure why this is not working. Does anyone know a fix to this or perhaps a better method to achieve this?
Thanks
Ok, so this looks like it's in a filter...I'll assume it is, but let me know if not. I think you can probably rewrite your if statement to be a calculation instead. It'll depend exactly what the rest of your statement was going to be, but I'll make a start on it and you can alter it according to your needs:
[Date] between
_add_days(current_date,-27-_day_of_week(current_date,1)) <-- This gives you the date 4 weeks ago on Monday
and _add_days(current_date,-_day_of_week(current_date,1)) <-- This give you the Sunday at the start of the current week
I haven't tested or checked that, so please try it to make sure it works etc.
It seems something like this may have fixed it. I ended up using a filter something like [Date] between _add_days(current_date,-28) and _add_days(current_date,-[Day of Week Current Date])
Thank you for your reply!