Using a dynamic variable to make/filter a selection (date field)

[xaelm]

I'm having trouble using a dynamic variable to filter a selection on a date field.

For example, this works:

While this does not:

The variable vRollingThreeMonths has a definition of: 

= '>=' & Date(Today()-90) & '<=' & Date(Today())

 which (today) evaluates to: 

>=10/20/2023<=1/18/2024

Why is this not working? I've also tried wrapping the variable in single quotes, brackets, starting with '=Date', etc. and still have not been able to figure this out.

Ultimately I would like to filter the sheet by a certain date range by default, while also still being able to use the date picker extension. To do this, I was going to: filter date field by desired range>create a bookmark using this selection>set it as the default bookmark.

Any help would be greatly appreciated!

[RafaelBarrios]

Hi @xaelm 

you are misssing to specify the field you are looking at

here an example

vGetLogRecord = 2023-12-27

Filter field name: Date

now using:

=Date='$(vGetLogRecord)'

and

=Month=Monthname('$(vGetLogRecord)')

Here a PDF you can download with more search options

https://www.linkedin.com/posts/rafaelbarriosbelisario_qlik-cheatsheet-activity-7054920286869045249-q...

Hope this helps,

Best

[xaelm]

@RafaelBarrios Thanks for replying!

I tried specifying the field in the search expression, but it still isn't working as expected:

 I also tried this with no luck:

=Date='$(vRollingThreeMonths)'

I believe the issue lies somewhere in the variable expansion and/or declaration because when I filter using static values (i.e. the value that the variable evaluates to) it works as intended:

Any thoughts?

[RafaelBarrios]

i see,

thats not going to work

this is what its doing

=Date='$(vRollingThreeMonths)'
=Date='>=10/20/2023<=1/18/2024'

look at the single quotes

Try This:

=Date={">=10/20/2023<=1/18/2024"}
=Date={"$(vRollingThreeMonths)"}

i can not test, so this can have some syntax error.

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 🤗

[xaelm]

Still no luck but I appreciate all your help thus far! Maybe I need to think of another way to get the functionality I need...

[RafaelBarrios · Accepted solution]

upsss

sorry, now i can test !!!

the only way a managed to get to works is as follows

=Date>='2023-12-30' and Date<='2024-01-02'
by using variables
=Date>='$(aMinDate)' and Date<='$(aMaxDate)'

having

or

having 

be aware on the equal sign on dates variables and not in DateEval2 variable

May I ask what are you trying to achieve? End users generally do not have access to variables definitions and dont use them for searching in filter objects.

hope this one helps.

Best,

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 

[xaelm]

Rafael,

Thanks for all your assistance, the solution you provided worked! The reason I wanted this functionality is so that the data in the dashboard dynamically filters to display just the last three months by default, but I also wanted users to be able to go back further if needed. I'm still pretty new to Qlik Sense so it might be a weird way of doing it.

How would you achieve that functionality?

Thanks again!