Time interval between two timestamps

I built a report that loads a database table of logged event data. The database table includes a datestamp field containing the date and time of the event. I wanted my report to have a chart showing the duration in hours, minutes, and seconds between the events shown on each row of the chart. That would allow me to make selections on types of events or the source of the event and the time duration between listed events would be useful.

I created a straight-table chart with a dimension of a field called EVTLOG_ID. That single field is a unique identifier that I could sort by descending value and guarantee that the rows in my chart are in chronological order with the most recent events at the top of the chart. Then I added an expression that simply said =EVTLOG_DATE to show the timestamp field from the table. Then I added another expression like this =time(interval([EVTLOG_DATE]) - below([EVTLOG_DATE])),'h:mm:ss') to show the interval between the timestamp field and the timestamp field on the lower line in the chart. The outer function, time, makes the format show as hours, minutes, and seconds without the unneeded "AM" label.

The chart works great – figuring out the elapsed time between timestamps is a tiresome chore to do in your head.

★ ★ ★

Easy Text Search

People like the feature in QlikView that lets you start typing a word or phrase on top of a listbox (or the pull-down for a particular field on a multibox) and QlikView automatically shows you a list of the fields that contain that word or phrase. Notice that when you begin typing, a small box appears that contains the word you typed in between two asterisks. For example, if you click on the listbox for product names and start typing the word “cheese” a box appears containing *cheese*
The feature is called a text search and the asterisks are wildcard characters. The asterisk represents any string of characters… so the list of values that QlikView is showing you in our example is any group of characters, followed by “cheese”, and followed again by any group of characters… which really means any value containing the letters “cheese”. If you wanted only product names that begin with the word cheese then remove that first asterisk (so that the box contains cheese* ) and you will see only product names beginning with the word “cheese”. Similarly, remove the second asterisk and you only see product names that end with the word “cheese”.

Another wildcard character you can use is a question mark, “?”. The question mark represents any single character. I use it all the time when the field is a date field in the form of mm/dd/yyyy… then , for example, I can type something like 10/??/2010 and it will show me a list of all of the date values in October 2010 (“10/01/2010”,”10/02/2010”, etc.)

(Author's note: Nov 4, 2010. With QlikView version 10 the default behavior of the listbox is not to automatically insert wildcard asterisks as you start to type text. But, the search behavior is very similar and you can still type your own wildcard characters as needed.)

★ ★ ★

Selecting Null Values

It’s true that you cannot select the null values of a field. But, you can usually do something that will work even better to identify the null values. The idea is that you select values of a different field that are associated with the null values you’re interested in. As always, to help communicate the idea, here’s an example:

Imagine that you have a report of sales data but there’s a defect in the data and some of the products don’t have a product description. You can’t make a selection on missing or null values in the product description field, named PROD_DESC for our example, but you can easily select the products that have a missing or null product description. Go to the listbox for the PRODUCT_ID field or click the pull-down arrow for PRODUCT_ID from a multibox. Now, start typing this expression:

=isnull(PROD_DESC)

click Enter to finish the selection and you now have a selection consisting only of products that are missing their descriptions.

You can use a slightly different expression to select products with a blank or null product description; type this instead =isnull(trim(PROD_DESC))

This technique is useful if you use QlikView to find data quality issues or to validate data from suppliers or customers.

★ ★ ★

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.

★ ★ ★

Fire a Trigger When User Changes an Input Field

Prior to the introduction of Input Fields there was no need for a trigger that would fire when loaded data changes. Because the only way that data could change was to reload it. But, when the QlikView document uses input fields then some of the data can be changed by the report user. The input fields feature allows data to be changed in list boxes, table boxes and some charts and can be very useful.

I helped advise a project that needed to know when data was changed by the report user because they had to apply custom logic for splitting the change from a summary level to detailed level. The solution was to use a variable containing an expression that looked something like this =Sum(sales_forecast) Then, a trigger was defined that would fire when the value of the variable changes. QlikView would evaluate the expression when the data changed and the trigger would fire causing a macro to execute which did the customized data split.

That solution worked well with QlikView 9.0 SR2 but when SR3 was installed on the computer then the trigger no longer fired. Without that trigger there is no way to run a macro when the data changes (if anyone knows of another way to fire the trigger when input field data changes please leave a comment below). The project is currently using 9.0 SR2 and everything works fine but they cannot upgrade to a later release unless this issue is solved for later releases.

[October 4, 2010 Note: The problem seems to be fixed in version 9.0 SR 6]

★ ★ ★

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.

★ ★ ★