Now that we have a general layout to start from, it is time to add another chart to the Analysis sheet. As you might remember from Chapter 3, Seeing is Believing, a new chart can be added by selecting Layout | New Sheet Object | Chart from the menu, right-clicking on the worksheet and selecting New Sheet Object | Chart, or clicking on the Create Chart button on the toolbar.

This opens the first page of the Chart Properties dialog: the General tab. On this tab we can set some general settings for the chart, such as what the display text in the caption (Window Title) should be, and, more importantly, what Chart Type we wish to create.

Another interesting option in this window is the Fast Type Change option. This option allows the user to dynamically switch between different types of charts, for example, we may switch between a bar chart and a straight table.

One of the required charts in our document should display number of passengers and number of available seats by flight type. We will use a bar chart to visualize this metric. Follow these steps to create it:

When we look at this chart, we notice that it's quite hard to read. The numbers are really large, all those zeroes occupy a lot of space. Besides that, the title text and caption both say the same thing and do not reference the second expression in the chart.

Let's correct these issues by changing the following settings in the Properties dialog:

The resulting chart should look similar to the following screenshot:

Now that we have formatted our chart, we can copy these settings to another chart using the Format Painter Tool. To do this, activate the object for which formatting needs to be copied and then click the Format Painter Tool button in the design toolbar. Next, click the target object to apply the format. Use it to copy the formatting options we set previously and apply them to our Traffic per Year chart.

Before we look at the other chart types and objects that QlikView has to offer, it is time to have a more in-depth look at Expressions and the Edit Expression window.

The Edit Expression window

The Edit Expression window is used to enter expressions. Whenever you see an ellipsis character (…) accompanying an input box, it means you can click on it to enter an expression.

Let's open the Edit Expression window now and have a closer look:

1. Right-click the # of Passengers/Available Seats chart and choose Properties....
2. Select the Expressions tab and highlight the # of Passengers expression from the list on the left.
3. Click on the … button next to the Definition input box.

The Edit Expression window is shown in the following screenshot:

The Edit Expression window contains a big input field in which expressions can be entered directly. Once you have familiarized yourself with the various expression functions and their syntax (we'll cover many of them throughout the book), you will realize that this is the fastest way to enter an expression. The Edit Expression window automatically checks the syntax of the entered expression; if an error is found, the expression will be underlined with a red squiggly line and the text Error in expression will be displayed.

Be aware that the automatic syntax check does not always work flawlessly; with advanced expressions, the editor will sometimes indicate that an error is present when in fact there is none.

At the bottom of the expression editor, a few tabs can be found. Let's quickly see what each of these tabs does.

Fields

The Fields tab enables "clicking together" an expression by selecting an Aggregation function, such as sum, avg, min, max, and the field to which it should be applied. The Table dropdown can be used to filter the field list to those belonging to a particular table.

When the Distinct checkbox is marked, only unique values will be considered in the aggregation. This can be useful when, for example, we want to count the number of distinct customers, instead of their total number of appearances in the database.

When all selections have been made, the expression can be entered into the Edit Expression input field by clicking on the Paste button. Note that the code will be pasted where the cursor presently is, and will replace any highlighted text in the expression.

Functions

While the Fields tab makes it possible to create expressions using just the mouse, it is fairly limited in the type of expressions it can create. The Functions tab, however, contains a comprehensive list of available functions, grouped by Function Category and Function Name.

Selecting a particular function will display its syntax in a box. The selected function can be entered into the expression input field by clicking on the Paste button, but the corresponding parameters have to be set manually.

Variables

As we will see later in this chapter, variables can be used to store expressions and values. The advantage of this approach is that we can use an expression in many places, while only maintaining it in a single place.

If, for example, instead of directly typing the # of Passengers expression into the input field we had created a variable containing its definition, we would be able to select that variable from the drop-down list on the Variables tab and achieve the same result.

Images

A QlikView expression does not always have to be text or a calculation. There are some objects, for example, the Text Object or even a Straight Table, that are also able to display the result of an expression as an image.

The Images tab makes it easy to select images that are built into QlikView, or which have been bundled into the document via script. Simply select an image name from the Image drop-down list or, more conveniently, from a visual menu of images by clicking on the Advanced button.

Clicking on the Paste option will enter a string referencing the corresponding image into the expression input field. These string values can also be used within expressions. For example, the following expression will compare the Target field using the if function. If the value is greater than 100, a green upwards arrow will be displayed, otherwise a red downwards arrow will be shown.

if(Target > 100, 'qmem://<bundled>/BuiltIn/arrow_n_g.png', 'qmem://<bundled>/BuiltIn/arrow_s_r.png') 

Click on Cancel in the Edit Expression dialog window to close it without saving any changes and close the Chart Properties window as well.

The Expression Overview window

With expressions in so many locations, it can be hard to keep track of them all. This is where the Expression Overview window comes in handy; it offers a central location to manage all expressions being used in our QlikView document.

The Expression Overview window can be opened by pressing Ctrl + Alt + E or by selecting Settings | Expression Overview from the menu bar.

By default, only Chart Expressions in the QlikView document are shown. This list can be expanded or narrowed down by (de)selecting the checkboxes for each expression type.

It is possible to edit an individual expression by highlighting it from the list and clicking the Edit button. Bulk updates are possible, using the Find/Replace button. Be very cautious when using this function, as unintended changes can occur.

The Line Chart works very much like the bar chart that we looked at earlier. So, instead of creating a new line chart, we will convert one of the already built bar charts into one.

Let's follow these steps to convert the Traffic per Year chart from a bar chart into a line chart:

The resulting line chart is shown in the following picture.

Notice we have to select a year for the months to be shown. In this case, we have selected 2011 in the Year listbox.

While this already looks quite nice, we will make a few extra changes:

Follow these steps to apply the changes:

The resulting line chart is shown in the following screenshot:

As you can see, the actual trend can be easily perceived and the individual data points are much more visible. Additionally, the scale on the y axis now contains much shorter numbers. The advantage of setting values for thousands, millions, and billions is that the y axis scale will automatically adjust to the appropriate range when updating the chart based on user selections.

Additional line chart properties

While in the previous example we looked at the most common line chart attributes, there are some additional settings in the Chart Properties dialog that are interesting to take note of.

Expressions

On the Expressions tab, the Accumulation option can be used to display a moving total. This means that instead of presenting individual values, each new value is added to the sum of all previous values. In the following chart, instead of the individual amount of flights for each month, we see the total cumulative amount of flights as of each period:

The other line you see in the chart represents the Average; this option and is set under the Trendlines section.

Style

On the Style tab, you can change the Look option of the line chart. Besides some 3D effects, an interesting visualization is the area chart (fourth icon from the top). Another useful setting, though admittedly not as useful as it is for bar charts, is the Orientation option. This allows you to change the orientation from vertical to horizontal.

Presentation

The Presentation tab offers options to change how the data is presented within the chart. Useful options are under the Line/Symbol Settings section; with these options we can change the Line Width option of the chart as well as the size of the symbols (as we saw when we added the dots in the previous chart).

For charts that have many values on the X-axis, a useful option is the Chart Scrolling option. By checking the Enable X-Axis Scrollbar checkbox and setting a value for the When Number of Items Exceeds parameter, a scrollbar is added to the chart whenever the number of values on the X-axis exceeds the specified amount.

Arguably the most useful option in this tab, however, is found under the Reference Lines section. This option can be used to integrate additional, straight lines to the line chart. A practical example would be to add a target reference to compare each data point to a predefined objective.

By clicking on the Add button, the Reference Lines dialog opens. Here we can set an expression for the reference line, set its label, and change some other settings with regard to formatting. The following screenshot shows an example of a static 900,000 flights target line, but of course a dynamic target could also be used if it is included in the data model:

Though it sounds fancy, the Combo Chart is nothing more than a combination of the bar and line charts that we used earlier. It brings together all the properties of both charts.

Let's look at how this combined chart works by converting the # of Passengers / Available Seat (x 1 million) by Flight Type chart that we created earlier:

The end result should look like the following chart:

One thing you may notice is that while we entered three expressions, only two are visible in the chart. This happens because we did not select any display mode for the Load Factor expression. However, we did activate the Values on Data Points checkbox, and that is why the value for Load Factor is shown in the chart.

You may also wonder about the expression that we used to calculate the Load Factor value:

Column(1) / Column(2)

This expression tells QlikView to divide the result of the first expression by the result of the second expression. You will understand that the order of the expressions should not be changed in order for this to work reliably.

By now, with three charts already created, our worksheet is becoming somewhat cluttered again. Time to do another round of reorganizing. The option of choice this time will be a container object in which we will group multiple objects together in the same screen space. The user will then be able to interactively switch between objects.

Let's put all three charts (or, two charts and a table) into the container object by following these steps:

The resulting container is shown in the following image. Notice how we can switch between charts by clicking the tabs on the bottom row.

You will also notice that the original charts are still on the worksheet, making it look even messier. We will remove these old objects by right-clicking on each of them and selecting the Remove option. A pop-up window will appear asking to confirm deletion of either only the selected object or all linked objects. Click on the Delete Selected button as shown in the screenshot below:

The reason this dialog message appears is that there are now two instances of the same object, and QlikView treats them as linked objects (one object sharing the same properties and IDs, but in different locations). We will look at linked objects in more detail later on in this chapter.

After we've removed all the duplicate charts and have properly aligned the container object, we will remove the container's caption by following these steps:

It is important to click on the container heading or buttons; otherwise we would not be opening the container properties but the properties of the currently active chart. Now we have space to add even more charts!

One of the analysis requirements we have to meet is to provide an insight into the relationship between the number of passengers, number of transported mail, and the number of performed departures at the carrier level. To visualize this we will add a scatter chart by following these steps:

The resulting chart is shown in the following screenshot. The Y-axis shows the number of transported passengers while the X-axis shows the amount of transported mail. The bubble size indicates how many flights (departures) have been performed by each carrier.

We can immediately see there are carriers that only transport mail, such as United Parcel Service, and those that only carry passengers, such as Southwest Airlines Co. In fact, most carriers seem to either do one or the other, not both.

Make a few selections on the Carrier's Operating Region listbox and you might gain some interesting insights. Also notice how the unit of the chart's scale changes between selections because we set the Thousand, Million, and Billion Symbol fields.

In our example, we used the Simple Mode option to create the expressions for the scatter chart. As the name implies, this allows for only simple expressions to be formulated. We can switch to the Advanced Mode by checking the Advanced Mode checkbox on the Expressions tab. This will change the view to the regular Expressions tab that we saw on earlier charts.

Now that we have set up the basic structure and the charts for our analysis sheet, it is time to add a few buttons for the user to interact with. QlikView allows us to execute an action, or a sequence of actions, when a button is clicked.

Let's start with a practical example. During analysis, a user will often want to clear their entire set of selections, or undo and redo single steps in their selection. Follow these steps to add a button that will clear the user's selections:

We have now created a single button that, when clicked, will clear all current selections.

As we saw while creating the button, there are a wide variety of actions that can be assigned to it. These actions can also be chained, so that one click on a button triggers a sequence of actions. The following screenshot shows a sequence of actions in which we first clear all selections, switch to a predefined sheet, and finally make a selection in a predefined field:

Of course, we still have to create the buttons for undoing and redoing a selection. The corresponding actions are found as Back and Forward, respectively. Take a minute to create the buttons for these actions as well and align them under the Current Selections box. If everything goes correctly, you should end up with something like this:

Test each button to make sure they are doing what they are supposed to do.

A statistics box is a convenient way to quickly perform a series of statistics on a single, numeric field. For example, the following shows the total, average, minimum, and maximum distances in a single statistics box.

Now whenever we make selections, the Distance statistics box will automatically show the various statistics calculated over all the individual records in the fact table.

With the added statistics box object, and after appropriately resizing and positioning objects, the analysis sheet should now look like this:

The Analysis sheet now meets all the current requirements. The objects we've created while building this sheet are the bar, line, and combo charts, a scatter plot, buttons, and a statistics box. We've also learned how to organize objects using a container and have had a closer look at chart properties, expressions, the expression editor, and expression overview.

Of course, QlikView has many other objects and functions that we can use in our documents. Let's move to our next sheet and discover some more of what QlikView has to offer.