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

[amitrathi239 · Accepted solution]

Create variable like 3Yr back date=RelativeDate(CurrentDate();-3;YearPeriod)

Final variable=If ([your date object] between ([3Yr back date];currentdate()) then "Show" else "Hide"

Apply the filter at Table where equal t Show

[matix10 · Accepted solution]

Thank you amit.kumar71 for your help as always! 🙂

[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! 🙂

[vishalakshmi]

Hello Bhagya,

Also please check the below blog:

https://blogs.sap.com/2018/11/18/web-intelligence-dynamic-date-filter-with-relative-values-prompt/

Thanks,

Lakshmi.

[matix10]

Hi Lakshmi, Thanks a lot, will go through this blog!

[amitrathi239]

You can do through creating 3year back date object in the universe.

After that add the webi filter where Date object is between 3year back date and sys date object.

e.g.

add_months( trunc(sysdate),-36)

[matix10]

I am not supposed to do anything in UDT. I need these changes to done at Webi level only.

[vishalakshmi]

Hello Bhagya,

Can you please up-vote if my answer is helpful to you.

Thanks,

Lakshmi.