Random Sample Selection

Here’s an idea that will certainly come in handy some day. You can make a selection of random values from any field in your QlikView document. For example, imagine that you have a document loaded with your company’s employee expense report data for the past five years. And, imagine that you want to select a random twenty percent of the employees. Just click on a listbox of employee names or click on a pulldown arrow of employee names on a multibox. Then, type this expression:
=rand()<=0.20

And you will be looking at a random selection of approximately twenty percent of the company employees. Hit Enter to complete the selection.

This idea can work with any field in the document. You can also use an advanced search dialog box instead of a listbox or multibox. This kind of search expression works within the existing selections – if you had already selected only the part of your company operating in Mexico then your expression will select a random twenty percent of employees in Mexico. If you already have some employee names selected then you might want to clear the selection before using the expression. And, like I wrote in my previous blog posting, you can save an expression like this in a bookmark.

Note: If you are going to be doing this random selection several times on the same document then it may give you the same group of values each time. To make sure you get a different set of values every time you use the expression type this slightly longer expression instead:

=rand()<=0.2+now()*0

(See the comments section below for a suggestion about how to do a random sample selection as the Calculated Dimension in a Chart)

★ ★ ★

Bookmarks for Dynamic Selection

I want to tell you about one of the cool things about doing a QlikView selection using a search expression. I’ve written about this topic before (Click here to read it). When you’ve made the selection with a search expression and then create a bookmark, the bookmark stores the expression and not just a static list of selected values. Then, the next time you click on that bookmark it re-applies the expression!

For example, if you click on a listbox of Salespersons and type =rank(sum(SlsHist))<=10 that will select the top ten Salespersons based on sales history (assuming that SlsHist is your field name for sales history). Then, create a bookmark. This will work best if the selection of Salespersons is the only selection currently made on the document. Now, every time you click on that bookmark it will select the top ten Salespersons. This works within the current set of selections, so, if you’ve already selected only products sold in Canada then when you click the bookmark it will select the top ten Salespersons of products sold in Canada.

You can export bookmarks, of course, and share them with others. I’ve started a small library of these search expression bookmarks that are useful for finding various data issues that need research. I just export the bookmark to a file whenever I create a new one that might be useful again someday.

If you click on one of these bookmarks and nothing gets selected then it means that nothing in the document satisfies the expression criteria.

Now, another cool thing: Beginning with QlikView version 9.0 you can create an Action that applies a bookmark. So, you could define an action that applies one of your search expression bookmarks and apply the Action to a Button or to the On Open event for the document. Using this technique you could make your document do a special selection whenever the document is opened (without writing any macros). Maybe you could make your executive dashboard show the ten most profitable customers over the past week whenever the document is opened.
.
Notes on how to write those expressions:

These are expressions that QlikView evaluates as either true or false. If you have a listbox of your company's Products and you type an equal sign, = , and then the expression; QlikView will evaluate the expression for each Product. If the result of the expression is true then the Product is selected, if the result is false then the product is not selected.

For example, you might type =count(distinct repair_orders)>100 on your listbox of Products and QlikView will select Products with more than 100 repair orders in the report data.
You might type =sum(invoice)<0 to find Products with invoices with negative amounts that need to be investigated. Or, on a listbox of hospital treatment dates type =sum(admissions)>sum(discharges) to find dates where more patients were admitted than sent home.

Mastering this technique for making selections will help make QlikView even more useful to you.

If you have any favorite or especially creative ways to use this technique please describe them in a blog comment (see below).

★ ★ ★

Listbox or Multibox for Group of Things That Satisfy a Condition

The expressions that I wrote about in my last blog post can also be used in a listbox or multibox. For example, the expression
=If(aggr(rank(sum(SHIPMENTS)),ITEM)<=15,ITEM,null()) which lists the 15 top ITEMs by shipment volume, can be put into a listbox where it will look like a regular listbox for the ITEM field except that it will only show the top 15 ITEMs. [in this example the field SHIPMENTS is the number of cases of product shipped and ITEM is the product or material name]

If you have never used an expression in a listbox before, then go to the listbox properties and open the General tab and scroll up to the very top of the list of fields, there you’ll see the choice for entering an expression.

One thing different here from the kind of listbox that lists a field is that this listbox with an expression will change as your selections change. For our example, it will list the top 15 frozen items when you have frozen products selected but when you change your selection to coffee products it will list the top 15 coffee items. Another difference is that you cannot change the listbox sort option to sort the list by State (meaning the state of whether a thing is selected or not) but you can make the listbox sort things by numeric or character value.

You can set up a multibox with several of these expressions. I have a report with a multibox that has several choices for someone who is interested in finding data problems. Similar to the expressions in the last blog posting, I have =If(sum(SHIPMENTS)<=0,ITEM,null()) that can show me ITEMs with no shipments and =If(sum(SHIPMENTS)<=0 and sum(FORECAST)>0,ITEM,null()) to show me a list of ITEMs with a sales forecast but no shipments, and =If(sum(PROMOTION)>0,ITEM,null()) to show me a list of ITEMs with special promotional forecasts.

So, this is another option for you if you or your report users if you regularly have to select a special group of things; the special groups can be defined in a listbox or multibox or as a calculated dimension as explained in the previous blog post. In my next posting I'll describe how to select a group of things that satisfy a condition using a bookmark.

★ ★ ★