topic Re: Tabular Reporting Date Filters in Reporting Service & Alerting

Tabular Reporting Date Filters

Matt_Leonard — Fri, 15 Nov 2024 21:06:21 GMT

Hi there

I have an app that has a bookmark on Year and Month using CommonDate which I am wanting to replicate in a tabular report.

When I add a filter for year using CommonDate and evaluate year(now()) the report is blank.

Is it also possible to use a date range in a filter? When I try to add <= or >= I get an error in the formula.

Any help would be appreciated.

Thanks

Re: Tabular Reporting Date Filters

anat — Thu, 15 Feb 2024 14:06:31 GMT

can you look into below links if you not yet gone through

https://community.qlik.com/t5/QlikView-App-Dev/Dynamic-date-selection-using-bookmarks/td-p/297778

https://community.qlik.com/t5/New-to-Qlik-Sense/Date-range-expression-in-default-selection-bookmark/td-p/1699348

https://community.qlik.com/t5/App-Development/Dynamic-Date-Range-Bookmark/td-p/1966799

Re: Tabular Reporting Date Filters

Matt_Leonard — Thu, 15 Feb 2024 14:13:54 GMT

Hi Anat

I am looking at Tabular Reporting in Qlik SaaS rather than in app bookmarks.

Thanks

Re: Tabular Reporting Date Filters

JonnyPoole — Thu, 15 Feb 2024 16:31:23 GMT

For the dynamic filter I was able to get it to work using 'evaluate' or 'search' filters

As you can see below for an 'evaluate' filter the preview is correctly resolving to '2020' which (in my example) is now()'s current year 2024 minus 4 = 2020.

This would not work on a 'Date' field though. I had to use it on a year field which was generated in the load script using something like this: Year([OrderDate]) as OrderDateYear

You can do a year range with a two part filter like this:

This filter ended up filtering my report to the years from between 5 and 6 years ago (in my case years 2018 and 2019 but not 2020+).

Re: Tabular Reporting Date Filters

Matt_Leonard — Fri, 16 Feb 2024 08:15:17 GMT

Hi Jonny

I couldn't get this to work due to the way that the app is set up but I have tried it on another app and it works perfectly.

I have managed to solve the issue with this app by creating 2 variables in the app:

$(vMonthStart) and $(vMonthEnd).

I then created a filter on CommonDate and using the 'search' function with the formula:

=CommonDate>=$(vMonthStart) and CommonDate<=$(vMonthEnd)

The report is now generating data as expected.

Many thanks

Matt