Question Re: Rolling Date Filter in Technology Q&A

Rolling Date Filter

matix10 — Thu, 21 Sep 2023 06:03:44 GMT

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!

Re: Rolling Date Filter

matix10 — Thu, 21 Sep 2023 06:05:10 GMT

Hi amit.kumar71 ,

Could you please help me with this ?

Thanks in advance 🙂

Re: Rolling Date Filter

amitrathi239 — Thu, 21 Sep 2023 08:37:36 GMT

what is the backend database?

Re: Rolling Date Filter

matix10 — Thu, 21 Sep 2023 08:45:43 GMT

Hi amit.kumar71 , It is oracle database

Re: Rolling Date Filter

amitrathi239 — Thu, 21 Sep 2023 08:58:46 GMT

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)

Re: Rolling Date Filter

matix10 — Thu, 21 Sep 2023 09:03:37 GMT

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

Re: Rolling Date Filter

amitrathi239 — Thu, 21 Sep 2023 09:08:10 GMT

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

Re: Rolling Date Filter

nscheaffer — Thu, 21 Sep 2023 14:19:20 GMT

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

Re: Rolling Date Filter

vishalakshmi — Thu, 21 Sep 2023 17:46:20 GMT

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.

Re: Rolling Date Filter

matix10 — Fri, 22 Sep 2023 12:23:54 GMT

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

Re: Rolling Date Filter

matix10 — Fri, 22 Sep 2023 12:26:08 GMT

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

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

Re: Rolling Date Filter

matix10 — Fri, 22 Sep 2023 12:26:57 GMT

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

Re: Rolling Date Filter

vishalakshmi — Fri, 22 Sep 2023 20:23:37 GMT

Hello Bhagya,

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

Thanks,

Lakshmi.