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.

★ ★ ★

Calculated Dimensions to Show Things Satisfying a Condition

We often use a Cycle Group for the dimension on our charts. That allows the report user to switch between different dimensions easily. I’ve found it useful to add a few extra calculated dimensions that only show a particular group of things – a group that is useful to the report user or a group that requires attention.
For example, I often include a calculated dimension that only shows the top 15 items by shipment volume (it could just as easily be the top fifteen salespersons, or top 15 most requested service kits, or whatever). The expression for a calculated dimension like that is:
=If(aggr(rank(sum(SHIPMENTS)),ITEM)<=15,ITEM,null())

You will need to check off the Suppress When Value Is Null button on the Dimension tab of the chart properties. If it is important to you for the totals in the chart to remain correct for the entire population then change the code slightly so that the dimension shows the top 15 items and one choice for all other items something like this:
=If(aggr(rank(sum(SHIPMENTS)),ITEM)<=15,ITEM, 'All Others')

With a chart dimension like that the chart changes as the report user changes the selection. When the selection is Country=FRANCE, for example, the chart shows the top 15 items sold in France, and if the selection is Product=CLOTHING then the chart changes to show the 15 clothing items based on shipment volume. If the report user wants to make a selection for the 15 items then that is as simple as dragging the cursor over the 15 items listed in the chart.

In my job we are always looking for things that adversely affect the quality of sales forecasts. One example of something like that are items on the forecast reports that have no shipments. Unless the item is a very new product it should have shipments. When there’s no shipments for a forecasted item that often is a clue that there is a data problem. A calculated dimension for items with no shipments might look like this:
=If(sum(SHIPMENTS)<=0,ITEM,null())

A more specific calculated dimension expression that looks for items with a forecast but no shipments might look like this:

=If(sum(SHIPMENTS)<=0 and sum(FORECAST)>0,ITEM,null())

Similar calculated dimensions might look for items with no sales orders or no bill-of-materials or missing pricing data, etc.

Consider using a calculated dimension when your report users often have to review data for a particular group of things. The calculated dimension may be able to easily isolate a group of things that satisfy a condition even when doing the same task as a selection is complex.

In my next blog post I'll describe how to show and select a group of things satisfying a condition with a listbox or multibox.

★ ★ ★

Export Data From QlikView Table

If you have a QlikView document already loaded with data you can export the data from any of the QlikView tables inside the document to a file. The file can either be a comma-delimited text file or a .qvd file.

From the menu, choose File->Edit Script to open up the loadscript edit window. Insert a few blank lines at the top of the loadscript. Now, type in a Store command and an Exit Script command. For example, if the table you want to export is named 2009_HISTORY then you might type:
Store 2009_HISTORY into exported_2009_history.txt (txt);
Exit Script;
Don’t save the document with the loadscript changes. It isn’t necessary. Now, from the menu choose File->Partial Reload. The partial reload will execute the store command and then exit. On a large table this might take a few minutes to write out the file.

If you wanted to export the data into a QlikView .qvd file then use (qvd) on that Store command instead of (txt). Once the file has been exported it is easy to load the qvd file into a different QlikView document.

If you only wanted to export a few fields from the table then the fields can be specified. For example, to export just a few fields from our example table we could code:
Store Material, Customer, Invoice_number from 2009_HISTORY into exported_history.txt (txt);

You can export data to a flat file from a table box, too, of course. But, sometimes, when you have a large table you can't display the entire table in a table box and this partial reload method would be your only option. The other problem with a table box is that it doesn't really display all of the rows from a table; it shows the field values whether they come from a single QlikView table or not. This partial reload method will export data from a single table only.

★ ★ ★

Select Top Ten ITEM Values

I've mentioned several times the kind of expression that you can enter into a listbox or multibox to make QlikView do more complex selections than just clicking on values. Those expressions that you begin with an equal sign, =, are actually advanced search expressions and they can be very useful. Here's one more that helps answer a question that I get many times:

If you want to select the top ten or top twenty things based on a specific criteria there is a way to do it. For example, if you have a product identifier named ITEM and you have a sales history quantity named SHist then you can select the top ten ITEM values based on the highest total sales like this - first click on the ITEM listbox to activate it or click the down arrow for ITEM on your multibox. Then start typing this expression:
=rank(sum(SHist))<=10 and hit enter and it will have selected the top ten ITEM values based on sales history.

I'll have more to say about showing and selecting a group of things that satisfy a condition in my blog postings in August and September.

★ ★ ★

Stepping a Selection Through Each Value of a Field

Two years ago I posted an entry in this blog describing a macro that would let you step through all of the values of a field. The macro would successively select each value of a field. Shortly after I posted that entry the need for it became obsolete. QlikView has a much better method for stepping through the values of a field and it’s much easier to use than a macro.

Choose a listbox for a field that has the values you want to step through. Select one of the values. Now, just click the Down Arrow on your keyboard. QlikView moves the selection down to the next value. Every time you press the down arrow the selection moves down to the next value. Try it now – it’s easy! You would use this technique while watching the graphs and charts on your QlikView document and see the changes in the graphs and charts as the selection changes. You can quickly step through dozens of different products, or sales regions, or whatever you’re interested in. When you use this technique to step through date values it becomes a way to animate the charts and show behavior of data over time. The up arrow on your keyboard moves the selection the other way.

This is a case where the listbox works better than a multibox. You can click the drop-down arrow for a field on a multibox that contains a selection and then click the down arrow and it will move the selection, but then the list of values closes and you have to click the pull-down arrow again to get it back.

Now, try selecting several values at once on the list box… and then click the down arrow. The group of selections all move down one value. Now, try pressing the Page Down key on your keyboard and the group of selections move down several values. If this was a field of dates and you had selected seven dates then pressing Page Down would move down an entire week. The values that you select don’t need to be contiguous values and the technique still works.
Sometimes you might need to lock some of the other selections in order to better control the values that you step through.

★ ★ ★