Dynamically apply a default filter on a sheet

[LesJean]

Hello Qlik community,

I have a graph thats connected to a big table with data from every week since 2016. I want to display only the last 5 weeks. I've managed to hardcode it, applying to following filter automatically when entering the sheet:

='(25.10.2019|18.10.2019|11.10.2019|04.10.2019|27.09.2019)'

But, as you can imagine, this doesn't update automatically to the last 5 weeks.

I've seen ways to dynamically only select the last 5 weeks in the dimension. But, the problem for me is that I need it to be a filter because, sometimes, users needs to see further back than 5 weeks, and thus, still need access to the data from previous weeks, so it needs to still be in the selection.

Is there any way to dynamically filter on the last 5 weeks?

Thank you,

LesJean

[Stoyan_Terziev · Accepted solution]

Hello Les,

There are few approaches to solve your issue. Here is the one you will apply with ease. Just change 'Date' field with your field name. (Keep in mind this will only work if all your dates are set as proper dates during the data load.

=chr(39)&CONCAT(DISTINCT {<Date={">=$(=Date(WeekStart(Max(Date))-35))"}>} Date, '|')&chr(39)

This will create the filter you are applying dynamically (you can test it in a Text Object first).

Essentially this creates dynamically the search string you use for your selection trigger.

Kind regards,

Stoyan

[Jacob_Poole]

You might be able to create a variable that calculates the last 5 weeks. Then continue with your plan. On sheet entering the sheet apply the filter, using the variable value rather than the hard coded values.

[LesJean]

Hello Stoyan,

Thank you very much for your answer, it works!

As a reference, I just had to make a very small adjustment or else it wouldn't work, which is adding the opening and closing parenthesis, like so:

=chr(39)&chr(40)&CONCAT(DISTINCT {<Date={">=$(=Date(WeekStart(Max(Date))-35))"}>} Date, '|')&chr(41)&chr(39)

Thanks again for your quick answer!