Select multiple range values

[vishwaranjan]

Hi Team,

I have below scenario,

Source Table:

Week Type
201810 History
201811 History
201812 History
201813 History
201814 History
201815 History
201816 Forecast
201817 Forecast
201818 Forecast
201819 Forecast
201820 Forecast

And I have below variables:

vHistStart = 201812

vHistEnd = 201814

vForecastStart = 201817

vForecastEnd = 201818

Now my requirement is that, the Week field values should be select from 201812 to 201814 and from 201817 to 201818.

Means 201812,201813,201814,201817,201818 should be selected in Week field.

I have tried the below possible solution but not working, could you please help on this?

For this , I have created the Button and in Action==>select in field,

write field = Week

Below expressions have tried but not working

'>=' & vHistStart & '<=' & vHistEnd & '|' & '>=' & vForecastStart & '<=' & vForecastEnd

'>=' & vHistStart & '<=' & vHistEnd & 'or' & '>=' & vForecastStart & '<=' & vForecastEnd

'>=' & vHistStart & '<=' & vHistEnd or '>=' & vForecastStart & '<=' & vForecastEnd

Br

Vishwa

[vishwaranjan]

Hi Team,

Could you please help on this?

Br

Vishwa

[jensmunnichs · Accepted solution]

So I had a look around, and I couldn't find a simple way using loads of &'s to make this work. The 'Select in field' actions seems to work the same as the search option list boxes have, and I tried to make it work but I don't think there is any way to search for multiple date ranges at the same time.

However, using concat() and set analysis, we can create a search string to enter into the selection. Try using the below formula as your search string for the action:

='("' & Concat({<Week = {">=$(=vHistStart)<=$(=vHistEnd)"}>+<Week = {">=$(=vForecastStart)<=$(=vForecastEnd)"}>} Week, '"|"') & '")'

The Concat() function combines all Week values that meet the date range criteria and separates them with '"|"'. The '("' and '")' are necessary for the opening and closing brackets and the opening and closing double quotes for the first and last values for Week.

For the variable values you provided the result would look like this: ("201812"|"201813"|"201814"|"201817"|"201818"), which is a search string that also works when it is entered into the listbox search field for 'Week', hence it also works in the 'Select in Field' action.

[Brett_Bleess]

Vishwaranjan, did Jens' post help you get things where you wanted them?  If so, please be sure to mark his comment as solution by clicking the Accept as Solution button.  If you are still working on things, please provide an update, and if you figured something else out, please consider documenting what you did and marking that as the solution so others can learn from that.  

The only place I could point you that may have some further examples for ideas is the following, just use the search dialog box to look around for things:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett