Qlik Sense Sheet Action - selecting multiple values including Year(Today())

[Cori]

Hi,

I'm using sheet action to preset filters to my sheets. It's working just fine for certain single or multiple values or for Year(Today()).
But now I was trying to combine a certain value and Year(Today()) and I just can't get it to work no matter what. I tried with different separators, quotes, expressions,...

I want to select the current year AND a specified year in the future, e.g. 2025, from field "year".

Thanks in advance for your help!

[Andrei_Cusnir · Accepted solution]

Hello,

I am not 100% sure that I understood the entire use case scenario properly. However, here is a similar use case scenario where the selections are applied on 2 different date ranges. Base on Today() and on selected Year value. In case it is helpful, you can take a look at the implementation and modify the idea based on your needs.

1. Load a dataset with values from 3 different years (Today and future dates)

2. Create a variable with a default selected year

 

3. Create an Variable input object:

• Appearance > Variable > Name: vFutureYear
• Appearance > Variable > Show as: Drop down
• Appearance > Values > Fixed or Dynamic values: Dynamic
• Appearance > Values > Dynamic values: =Concat(distinct {<Date={"*"}>} Year(Date), '|') //Will help you list all the potential years from the dataset and avoid limit the values based on selected dates

4. Create a button (You can use sheet selection if you want etc.)

• Actions and navigation > Add action > Select values matching search criteria
• As field use "Date"
• As Value use: ='(*$(=Year(Today()))*|*$(vFutureYear)*)'
• This will select all the dates where the year is either current year and the one selected from the drop down list.

5. Outcomes:

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 

[Cori]

Hi Andrei,

thank you so much, that worked perfectly for me! 👌

I think the * in the values did the trick what is their exact purpose here?
With your solution I can either use variable as suggested (which probably is the better way 😆) or even set the value directly as follows
='(*$(=Year(Today()))*|*2025*)'

[Andrei_Cusnir]

Hello,

I am happy to hear that the suggested solution has helped you resolve the issue. Allow me to elaborate in detail as to how the "*" are used.

The action "Select values matching search criteria" will try to match all the values of the field that are "2022" or "2025". This will fail in case the values that you have are:

• 1/1/2022
• 2022/1/1
• etc.
• Because 2022 is NOT equal to 1/1/2022 etc.

Therefore, "*" means that we want all the values whether the year is found on the left side or on the right side of the entire value.

I hope that this information was helpful.

[Cori]

Hello,

oh my, that helped a very lot! I do know the * for using as wildcard, I just didn't realize my field values actually are NOT just the year 🙄

Thanks so much for getting me on track again 😀