Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
LesJean
Contributor III
Contributor III

Dynamically apply a default filter on a sheet

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

Labels (3)
1 Solution

Accepted Solutions
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

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

View solution in original post

3 Replies
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

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
Contributor III
Contributor III

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
Contributor III
Contributor III
Author

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!