Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Select multiple range values


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

1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Re: Select multiple range values

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.

View solution in original post

3 Replies
Highlighted
Contributor
Contributor

Re: Select multiple range values

Hi Team,

Could you please help on this?

Br

Vishwa

Highlighted
Creator III
Creator III

Re: Select multiple range values

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.

View solution in original post

Highlighted
Digital Support
Digital Support

Re: Select multiple range values

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.