Activate Multiple Listboxes

When I'm teaching new users about QlikView I always demonstrate how you can click on a listbox to activate it and then start typing a few characters and the values of that field containing those characters are displayed and can be easily selected. I also show them how if you start typing an expression like =Sum(Sales)>10000 it can help display and select products (or regions or salespersons or whatever) with sales greater than 10,000.

Clicking on the particular listbox to activate it is a simple but important part of the process. It is possible to activate more than one listbox at a time. And, if you do that, and start typing characters then QlikView will display the values containing those characters for all of the active listbox objects. You might not be able to hit enter and do the selections for all of the listbox fields at once because they will often interfere with each other.

The way you would activate several listbox objects at once is to click your mouse on the screen and holding the mouse button drag a "box" around the objects you want to activate. Or, you can activate all objects on the screen by pressing Ctrl-A. This feature may be useful when you are selecting multiple field values to match how they are selected in a document bookmark without implementing the entire bookmark (see the previous blog posting from June 15).

★ ★ ★

Restoring the selection for a single field from a bookmark

When you create a QlikView Bookmark to save your current selections it saves all of the selections for all of the fields where you’ve made a selection in your document. Often, that’s the reason you’re saving the selections as a bookmark – because it’s a complex set of selections for several fields that would take a while to recreate the next time you need them.

Sometimes, it would be nice to restore the selection from a bookmark for just one field. For example, if you have a bookmark named Project_3 that was used to save a complex selection from your monthly sales report, you might want to use only the part of that bookmark that is a selection on the Ship_Date field.

The way you would do that is to click on a listbox in your document for the Ship_Date field or click on the Ship_Date row in a multibox to activate the object. Then, start typing, make sure you begin typing with an equal sign (so that QlikView knows that you are beginning to type an expression). Type this:
=sum( { Project_3 } 1 )

That expression when typed into the selection box for Ship_Date will select only the Ship_Date values that are saved within the Project_3 bookmark. An expression like that can also be used with the Advanced Selection dialog box.

Remember that bookmarks can be exported and imported into other documents. You can even import bookmarks into documents quite different from the original document where the bookmark was created; when you do that the bookmark selections will be applied only to the fields named exactly the same as in the original document.

★ ★ ★

User Option done as a Field Selection

I’ve prepared a number of reports where the person who opens the report must make a choice about something that affects the report functionality or something that affects the way the report objects look. There are several ways to approach that issue when you design a report. Here’s an example from a recent report I designed.

The report had a chart in it that could show data by various units of measure or “UOM”. The unit of measure could be Cases or Eaches or Dollars and the chart object expressions would handle the conversions. We decided that we’d like the user to be able to change the UOM selection in a Multi-box on the tab so that the user could make all of her selections in one place. UOM doesn’t actually appear in the data being loaded so I had to put a little in-line table into the loadscript that defined the three values. It looked like this:
UOM_SELECTION:
Load * Inline
[UOM
Cases
Eaches
Dollars];

Then I loaded a new set of data into the report. Now, I could add the UOM field to the multi-box and the user could treat it the same as any other selection.

The Multi-box object has a feature on the Presentation tab that works well with this kind of field. If you first select one of the values of the field you can open up the Multi-box Properties dialog and choose the Presentation tab and click the Always One Selected Value check box (If you don’t first select a single value then the checkbox will be grayed out). This helps the user avoid making a mistake with the UOM selection – the user is prevented from clearing the selection in that field and prevented from selecting more than one value. Since the UOM field selection is only for one value, an expression in the chart can refer to it simply by name; for example, the expression might say If(UOM='Dollars',sum(sales)*unit_cost)

I made two other changes to the chart just to make sure that there would be no confusion about the UOM value:
I opened the Chart Properties dialog for the chart selected the General tab and put this expression into the Calculation Condition:
=if(count(distinct UOM)=1, 1, 0)
That will check to make sure that one and only one value of UOM is selected otherwise the chart will show an error message. Then I modified the error message text from the default value of “Calculation condition unfulfilled” to a more useful message that said “Please select a single UOM value to see this chart”.

★ ★ ★

Combining Bookmarks

I was called to help with a situation last week where one person, working on a pricing project, had analyzed some data and identified a group of materials that would be needed later. The materials were selected and a bookmark was defined.

There was a second person working on a seemingly unrelated marketing project, who also identified a group of materials, selected them, and defined a bookmark for the selection.
The problem was that there was a need to combine the materials selections from the two separate bookmarks. There’s no good way to really combine two entire bookmarks and create a third bookmark out of them. But, when you’re interested in the selections for a single field as the people in my example were interested in the materials field, then there is a technique for combining the selections.

To illustrate the technique, assume that the first bookmark is named BK1 and the second bookmark is named BK2. First, get both of the bookmarks into the same document. An easy way to do that is export the bookmarks from the documents where they were created and then import one or both of them into a second document. In the document containing both bookmarks you will need a listbox for the materials field or a multibox that contains the materials field.
Click on the materials listbox or on the little black triangle next to the materials on the multibox. Then, begin to type this expression:

=sum( { BK1 + BK2 } 1 )

As soon as you type the equal sign, QlikView knows that you are entering an expression. It looks like an expression you might use in a chart but we’re going to use it here to select materials. The syntax is a set analysis expression – it can be a little tricky with the parentheses and curly braces but it is worth the trouble when you need it. As you type the expression a number of materials will be listed with a white background – those represent a combination of all of the materials from both of the bookmarks. Finish the expression by hitting the Enter key and the materials are now selected and show up with a green background.

A similar expression could be used to combine the materials from a bookmark with the currently selected materials in a document. For that you would follow the instructions as above but type this expression:

=sum( { $ + BK1 } 1 )

If you wanted to select only the materials that appears in both bookmarks (the intersection), then type this expression:

=sum( { BK1 * BK2 } 1 )

And, finally, if you wanted to select the materials that are both in your current selection and in the bookmark then type this expression:

=sum( { $ * BK1 } 1 )

★ ★ ★

The Case of the Disappearing Object

A few weeks ago my co-worker Kim called me about a problem she was having copying a chart from one report to another. Copying any QlikView sheet object from tab to tab or report to report is very easy but she was having a problem pasting the object into a new report. There was no error message but it was obvious that the chart was not being pasted successfully.
We discussed the possibilities and I focused on security setting issues but, by the end of the call, nothing had worked.

Two hours later, Kim called me back – she had figured it out and, like many good problem solutions, it seemed so obvious in hindsight. The problem was that the object had a Show Condition defined that would make the object visible only under a specific data condition. The data in the new report was different and the condition was not satisfied. So, the object was behaving as the Show Condition directed and it was simply not visible in the new report. Now, I know to be conscious of the Show Condition when copying an object.

★ ★ ★