I am working on a reporting requirement where I need to fetch data for the previous 6 months. I am applying below expression for date column.
Date
between
_first_of_month(_add_months(current_date,-5)) and
_last_of_month(_add_months(current_date,-0)) .
But it is not fetching the required records. Please guide me if the filter is not corect.
Quote from: devardekartb on 30 Oct 2015 09:05:42 AM
...it is not fetching the required records...
So what is it fetching? How does the result set you are getting differ from the one you desire?
MF.
That filter looks good to me...it'll be selecting the records from the 1st of May to the 31st of October. What date range were you looking for?
Suppose the user is running the report on 16th october, it should give data from 15th April to 15th October.
Quote from: devardekartb on 30 Oct 2015 12:07:46 PM
Suppose the user is running the report on 16th october, it should give data from 15th April to 15th October.
So what is it fetching? How does the result set you are getting differ from the one you desire?
It's good to tell us what it *should* return, but so far you haven't given any of us any information that describes what it *actually* returns.
MF.
Quote from: devardekartb on 30 Oct 2015 12:07:46 PM
Suppose the user is running the report on 16th october, it should give data from 15th April to 15th October.
It would have been easier for people to understand what you need had you explained clearly what you need. You should also try to understand what is it that your users want. There is a difference between Last 6 Months and Rolling 6 Months. Based on the data range you provided, you need Rolling 6 Months. Last 6 Months means completed 6 months prior to the current month.
If you try to understand each part of the expression you are using, you would be able to figure out what you need. Try this:
Date >= _add_months( current_date -6 ) and Date < _add_days(current_date,0)
If this results in a Date range which starts one day after the date you need then add the _add_days function to _add_months to subtract one day. I am not writing it here to encourage you to develop the ability to figure it out.