Qlikview – Inputbox as a filter

Recently, I was working with a client who wanted to make it easier for end users to be able to do complex associative searches in a list box.
A few examples of how associative searches can be used in order of increasing complexity:

1) Search for all Suppliers whose name starts with the letter S
qv_inputbox_1
2) Search for all Suppliers who have products in the Baby Clothes category
qv_inputbox_2
3) Search for all Suppliers whose product sales add up to over $1M
qv_inputbox_3

We could continue and add more complexity with set analysis or nested if functions.

As you can see, this is a powerful feature, but probably only something we would expect experienced power-users to utilize regularly. If the users have one particular question that they need to filter the data to obtain, we can create a button with actions that do the search for them based on a variable. For this example I assume the users want to: Show products whose sales add up to over $X.

qv_inputbox_4

In order to make this possible, I followed these three simple steps:
1) Create a variable (vMinSales) that will store the desired minimum sales amount (Under Settings>Variable Overview)
2) Create an inputbox object on the sheet and choose your new variable (vMinSales) as the displayed variable
3) Create a button or text box
.     a. Add an action to Clear the desired Field
.     b. Add an action to Select in Field using the following String:
=’=Sum(LineSalesAmount)>=$(vMinSales)’
qv_inputbox_5

A quick warning

If you are using set analysis or any other expressions that require you to use an apostrophe (‘) then this would break up the search string. Qlikview doesn’t have a way to escape apostrophes in a string, but there are other options.
1) Use double-quotes (“) instead of apostrophes (‘)
2) Use chr(39) instead of an apostrophe in your expression. Ex: To render Men’s Clothes, you can use =’Men’ & chr(39) & ‘s Clothes’

Comments are closed.