Rolling Date Filter

[matix10]

Hi All,

I want to apply rolling last 3 years data filter in my report. I have gone through the available threads and tried to implement it but not able to get the expected result.

I have to rolling filter data from current date to last 3 yrs data. Can somebody please help me with this?

Thanks in advance!

[matix10]

Hi amit.kumar71 ,

Could you please help me with this ?

Thanks in advance 🙂

[amitrathi239]

what is the backend database?

[matix10]

Hi amit.kumar71 , It is oracle database

[nscheaffer]

Both of amit.kumar71's answers will work. However, the variable method will result in returning more data than necessary which could perform poorly depending on how large the table is from which you are querying data. Now you could arbitrarily have a query filter to hard code a date three years ago in addition to the variable. Initially that would perform fine, but performance would degrade over time.

Another option would be to create a free-hand SQL query as follows...

SELECT add_months( trunc(sysdate),-36) as "TodayMinus3Years"<br>FROM dual

You can then use that value in the query filter of your main query

If you do not have permission to create a free-hand SQL query maybe you could create universe query with a singular object with Date Time data type and then drop the select statement from above in as custom query script.

Noel

[matix10]

Hi Noel, Thank you so much for this valuable input!

Will try with this as well! Thanks much again! 🙂