Hello,
I am currently working on a report in which I need to display a minimum of the most recent 90 days of information by the week ending date while maintaining complete weeks. 90 days comes out to be almost 13 weeks. Thus, I need to create a filter which will only give me complete weeks (starting on Sunday, ending Saturday).
Currently my filter looks like this:
[Date] between
_add_days ( current_date, -90)
and
_add_days ( current_date, -0 )
The issue is that depending what day it is ran, complete weeks may not be possible. I then attempted to create a similar filter which looked like this:
_week_of_year([Date]) between
_week_of_year([ _add_days ( current_date, -90))
and
_week_of_year([ _add_days ( current_date, -0 ))
The issue with this however is that it displays information from previous years which are of the same week number. I cannot filter out those years because I still need it to return information from the previous year which happens to be within that 90 days requirement. Does anyone know of a way to fix this? It does not have to show exactly 90 days but it must show 90 at minimum to clarify.
Thanks
The way I'd probably go about it is to work out the date for the most recent Saturday, then subtract 91 days off that. I haven't tested it, but it would be something along the lines of:
[YourDateField] between
_add_days( _add_days(current_date,-_day_of_week(current_date, 1)) ,-91) and _add_days(current_date,-_day_of_week(current_date, 1)-1)
This worked perfectly.
Thank you!