QlikView Charts and Tables

Working with Charts and Tables in QlikView

1. BAR CHARTS AND PIE CHARTS

Introduction

Charts and tables are sheet objects that can show numbers very compactly. Charts, pivot tables and straight tables are logically the same thing, even though they look different. Hence, we will from here on simply refer to them as charts. Charts can thus be shown as bar charts, pie charts, scatter charts, line charts, combo charts (bar/ line), radar charts, grid charts, gauge charts, straight tables or pivot tables. All the chart types are shown below.

Opening the document

If you closed the document and exited QlikView after the previous lesson, you need to open it again.

Start QlikView by double-clicking the QlikView icon.
Select the file you created in the previous chapter, named MyTutorial.qvw or something similar, directly from the File menu.

Making selections in a chart

It is however also possible to select data in charts.

Go to the Geography sheet.

The Geography sheet contains a bar chart showing the ten largest countries.

Place the cursor in the chart, then press the mouse button and select a few bars by “painting” the area (dragging the cursor). When this is done, release the mouse button.
The countries represented by the bars you painted have been selected. Check this in the list box Country.
It is also possible to make selections by clicking on the labels in the chart: 4 Select one or several countries by clicking on their labels.

Moving and sizing

Charts can be moved, sized and copied in the same way as list boxes.

Place the cursor on the header of the chart, then press the mouse button and drag the chart to the desired position.
Size the chart by dragging its frame.

Copying, minimizing, maximizing and restoring

You copy the chart in the same way as the list box:

Press the CTRL key and keep it depressed while placing the cursor on the header of the chart.
Press the mouse button and drag the chart aside. While dragging, make sure that a small plus sign appears; if it does not, this means that you have released the CTRL key.

It is possible to minimize charts to save space. Do the following:

Click on the chart with the right mouse button, then choose Minimize

Changing chart type with fast type change icon

Some charts in QlikView are prepared for being displayed as more than one type. This is shown as a little icon, either in the chart’s header or in the chart itself.

Take a look at the bar chart in the Geography sheet. In the chart header to the left of the minimize icon you will find a fast type change icon.
Click the icon with the left mouse button. The chart will turn into a line chart.
This chart has been prepared for changing between three types of charts: bar, line and pie. If you click again the chart will turn into a pie chart.
Now click with the right mouse button on the fast type change icon. A dropdown menu will appear with all three possible chart types. Click the bar chart icon and we are back where we started.

Creating a simple bar chart

The toolbars contain two buttons for creating charts. The one in the main (upper) toolbar allows you to create a chart in two simple steps, whereas the one in the design toolbar makes it possible to set a great number of properties from the very beginning.
Two things are important to think about when making charts:

What do you want to look at? (What should the sizes of the bars in the bar chart correspond to?) The answer, in this case, is the Sales. This is set as Yaxis Dimension in the second box.
What do you want to group it by? (Which field values do you want to use as labels for the bars in the bar chart?) The answer, in this case, is per Country. This is set as X-axis Dimension in the first box.

You’ll start by creating a simple chart showing the sum of sales per country.

Go to the sheet Sales, and make the list box Country active by clicking its header.
Click the Create QuickChart button in the main toolbar.
The first page of the QuickChart wizard appears. It contains three icons representing different charts. Click on the icon representing the bar chart.
The second page of the QuickChart wizard opens.
Select the field Country in the combo box X-axis Dimension and the field Sales in the box Y-axis Dimension.
In the Function group below the boxes, you can choose between the functions Sum and Frequency. If you keep the preselected option Frequency, this will result in the chart displaying the number (total count) of sales performed in each country. This is not what you want. Select the option Sum to get the sum of sales per country.
Click OK.

You’ll soon be able to change this. However, you can already use the chart to make selections or to view the result of selections:
8. Select Ann Lindquist in the list box Salesmen.

Creating a bar chart using the full chart wizard

While on the sheet Sales, click the Create Chart button in the design toolbar. The first page of the chart wizard appears.
On this page you can choose the type of chart you would like to work with. The bar chart option is preselected; leave it that way.
Type Sales in the box Window Title.
Choose Next>

The second page of the wizard, Dimensions, on which you can set the dimensions to be shown on the x-axis, is now open. The left list contains all the fields or groups (you’ll learn more about groups on page 185) available.

Select the field Country, then click Add> to move it to the list of displayed fields.
Click Next >.

The Edit Expression dialog automatically opens. Here you set one or more expressions to be displayed on the y-axis.

In the Aggregation Functions and Fields group, click the arrow belonging to the first drop-down list and select Sum. Then click the arrow belonging to the second drop-down list (containing the field names) and select Sales.
Click Paste.
The name of the expression appears in the Expression edit box.
Type Sales into the Label box. This is the name that will appear on the yaxis.
Click OK. The sum of the company’s sales is calculated and will be displayed on the y-axis
By clicking OK, you get back to the Chart Properties dialog. Note that the expression you just defined has appeared on the Expressions page.
Click Finish
Clear your selections.

Removing a chart

Click on the first chart with the right mouse button, and then choose Remove from the float menu.
QlikView issues a message asking you if you really want to remove the
Click Yes.

Changing a few properties

The chart wizard that helped you create the chart contains eleven pages, of which you only used three. No need to worry: all the pages of the chart wizard are also found in the Properties dialog of the chart, which can be opened at any time.
Changing the sort order
The chart is currently sorted in alphabetical order.

Click on the chart with the right mouse button, and then choose Properties from the float menu.
Go to the Sort
Select the option Y-value to sort the countries according to their sums of sales.
Select Descending to put the highest bars to the left.
Click OK.

Limiting the number of bars

The chart you have created does not display any labels unless only a small range of values is selected. You can limit the maximum number of bars to be displayed:
Click in the chart with the right mouse button, then choose Properties from the float menu.
Go to the Presentation dialog page by clicking its tab.
Mark the check box Max Visible Number. The number 10 is preselected. Leave it that way.
Click Apply.

Displaying numbers on the bars

To display numbers on top of the bars in our chart.
Open the Expressions page in the properties dialog.
Select Numbers on Data Point.
Click OK.

You have now added the y-value numbers on top of the bars.

Changing the number format

You can solve the problem to show a large range on number on top of the bar by changing the number format.

Open the Number page in the Properties
Expressions default is preselected here. Change it to Fixed to.
Change the value in the Decimals box to 0 (if it isn’t set to 0 already).
Enter 1,000 $ in the box Thousand Symbol.
Click OK.

The numbers on top of the bars now have much more space.

Cloning and detaching your chart

You can clone (copy) a chart in the same way as a list box (by CTRL-dragging), but there is also another way of doing it:

Click on the chart with the right mouse button to open the float menu.
Click Clone. (A second chart, identical with the first one, appears on the screen.)
Move the chart in order to see all the sheet objects.
Click on the new chart with the right mouse button to open the float menu.
Choose Detach. (A detached chart is not updated as selections are made. This can be useful when you want to keep the overview while making selections.)

6. Make a few selections. See how the original chart is updated, whereas the detached chart stays the same.
7. Attach the chart again by choosing Attach from the float menu.
8. Clear your selections by clicking the Clear Selections button in the toolbar.

Turning the bar chart into a pie chart

Click on the chart with the right mouse button, then choose Properties from the float menu.
On the General page, select the option Pie chart.
On the General page it is also possible to change the chart title. Change it to Sum of Sales.
Go to the Presentation
Mark the check box Numbers in Legend (corresponds to Numbers on Data Points for bar charts).
Click OK.

The result is a pie chart where each slice represents the sales

Changing the color settings

This can be changed on the Colors page:

Click on the bar chart with the right mouse button, then choose Properties from the float menu.
Go to the Colors
Select the check box Multicolored.
Choose OK.

The 18 colors of the color map can be customized: on the Color page, simply click a color that you would like to change and pick the color of your choice from the map that opens.

Showing the percentage

Click on the pie chart with the right mouse button, and then choose Properties.
Go to the Expressions
Mark the check box Relative.
Click OK. (The percentage numbers now appear in the legend.)
Click on the pie chart with the right mouse button, and then choose Minimize. (The chart turns into an icon and is placed at the bottom of the screen.)

Saving, closing and exiting

Choose Save from the File menu. QlikView saves the changes you’ve made to your document. You can now close the file:
Choose Close from the File menu.

If you won’t be working with QlikView for a while, you can also exit the program:

Choose Exit from the File menu.

2. PIVOT TABLES AND STRAIGHT TABLES

Opening the document

If you closed the document and exited QlikView after the previous lesson, you need to open it again.

Start QlikView by double-clicking the QlikView icon.
Select the file you created in the previous chapter, named MyTutorial.qvw or something similar, directly from the File menu.

Adding a dimension to an existing bar chart

You’ll start by creating a chart with two variables and one expression. It will still show the sum of sales per country, but grouped over different years:

Clone the bar chart that you created in the previous lesson (Sales sheet).
Open its object (float) menu, and then choose
On the General page, change the chart title to Sales per Year.
On the Dimensions page, move Year to the list of displayed fields.
On the Style page, set Subtype to Stacked.
On the Presentation page, set 5 as maximum number.
Click OK to finish the chart.

A bar still represents the sum of sales of a specific country, but it is now divided into different color sections representing different years.

Turning the chart into a multidimensional pivot table

To display calculated data for several dimensions, your choice of chart may be a pivot table:

Open the Properties dialog of the bar chart you just created by choosing the corresponding command from the float menu.
On the General page, select Pivot table.
Choose OK.

The pivot table shows the data in cross table format: the first field (Country) is displayed as a regular column, whereas the values of the second field (Year) constitute the headers of the remaining columns. The columns contain the values of the expression (Sales).

Dragging dimensions

The pivot table is a very flexible sheet object, allowing you to freely drag the different dimensions and expressions to any position on the vertical or horizontal axis. In this case, e.g., you would probably prefer to present the dimension Year on the vertical axis.

Position the mouse cursor on the field Year.
Press the mouse button and drag the field to the desired position, e.g. to the right-hand side of the field Country. The selected column and its target are highlighted in blue while you are dragging.
Release the mouse button. (The dimension Year, as well as the expression values, are now displayed on the vertical axis.)

Adding a dimension

Open the Properties dialog and go to the Dimensions
Double-click the dimension Salesman to move it to the list of displayed fields, and then click OK. (The dimension appears on the horizontal axis.)
Place the dimension Salesman next to Year by dragging it the way you did above.

Expanding and collapsing

The pivot table provides yet another useful feature: the possibility of expanding and collapsing dimensions on value level. You have probably noted the small signs and displayed in the top right-hand corners of the values in the columns. A minus sign indicates that the dimension is expanded, whereas a plus sign indicates that it is collapsed.

Click in the Year column with the right mouse button, then choose Collapse all.

All the values of the dimension Salesman, previously shown, are now hidden. Suppose that you are only interested in the sales performed in Belgium:

Right-click in the Country column, then choose Collapse all to show only this column.
Click the plus sign of the value Belgium.
Click the plus sign of the values 1994 and 1995.

You have now expanded only those values of the following columns that are related to the value Belgium. Details on salesmen are only visible for 1994 and 1995.

Adjusting the columns

Place the cursor on the line separating the country column from the year column.
When the cursor looks like the one shown in the figure below, press the mouse button and drag.
All the columns can be sized this way. To adjust the rightmost column, place the cursor within the border (to the left of the scroll bar) and drag.

You can also adjust the columns using the command Equal Column width in the float menu.

Showing partial sums

Suppose you wish to know the sum of sales during all the years together. Do the following:

Choose Properties from the float menu.
Go to the Presentation
In the Dimensions and Expressions list, select the variable Year.
Mark the check box Show Partial Sums.
Click OK.

The pivot table now shows partial sums on year level.

Creating a straight table

In opposition to the pivot table, the straight table cannot display sub-totals or serve as a cross table. On the other hand, any of the columns of the straight table can be sorted and each of its rows contains one combination of dimension(s) + expression(s).

Minimize the pivot chart on the Sales sheet to increase the free space.
In the Layout menu, point to New Sheet Object, and then choose Chart.
In the wizard that opens, select Straight table.
Type Salesman in the box Window title.
Click Next>.
On the Dimensions page, move Year, Country and Salesman to the column of displayed fields.
Click Next>.
The Edit Expression dialog opens.
Compose the expression Sum of Sales by selecting the corresponding items from the lists in the combo boxes (Aggregation Functions and Fields group).
Click Paste.
Type Sales in the Label
Click OK.
Click Finish.

Sorting the table

Currently, the column Year is placed furthest to the left, and the table is sorted according to the sort order specified for this field (Sort page). You can see this from the little arrow sort indicator in the column header. You can change the sort order of the table with two simple clicks of the mouse:

Click on the column Salesman with the right mouse button to open the float menu.
Choose Sort.

The order of the columns remains the same, but it is now the sort order defined for the field Salesman that determines the order of the values in the table. Note how the sort indicator has moved to the Salesman column. The sort priority can also be set on the Sort page of the Properties dialog.

Moving a column

Suppose you want the field Salesman furthest to the left. Do the following:

Press the mouse button while on the column header Salesman.
Drag the column to the desired position. Both the selected column and its target are highlighted in blue while you are dragging.
Release the mouse button.

Visual cues

You can use visual cues to highlight expression values in the table.

Click on the straight table with the right mouse button, then choose Properties from the float menu.
Go to the Visual Cues
Sales is the expression available. Select it in the text box.

There are four value categories to choose between: upper, normal, lower and text. Suppose you want to highlight all the expression values above 10,000:

Type 10000 in the Upper > edit box.

Next to the edit box, you find two color buttons and three check boxes. This is where you set the appearance of the text and/or the background of the values you wish to highlight.

Suppose you want to apply a green color to the values belonging to the upper value category. Click the Text button, then choose a green color from the color map.
Choose OK.
In addition, check the Bold check box.
Click OK. (All the expression values above 10,000 are now highlighted.)

Selections in table charts

It is of course possible to make selections in pivot tables and straight tables as well.

Click the value 1992 in the Year The effect is the same as selecting 1992 in the list box Year.
Clear your selections.
Click the value $11,379 in the column Sales. You have now selected the value 1995 in Year, the value Pakistan in Country and Ann Lindquist in Salesman. Clear you selections.

If you need to make more complex or multiple selections in a table chart there is yet another option, called drop-down select. This feature makes it possible to turn a dimension column into a list box with full selection and search possibilities.

Click the Salesman straight table to activate it. Then select Properties from the float menu.
Go to the Presentation
Mark Year in the list of Columns and check the check box Dropdown Select.
Repeat for the Country and Salesman
Click OK.
Click the leftmost icon and a temporary list box with all the years will appear. Hold down the CTRL key and click the years 1992,1995 and 1997. Then release the CTRL key. The three years will be selected and the drop-down list is closed.
Click the drop-down icon in the Country When the drop-down list appears, type “sw”. This text search will result in Botwana, Swaziland, Sweden and Switzerland. Press ENTER and the two countries are selected while the drop-down list appears.
Clear your selections.

Moving the pivot table and the straight table to a new sheet

The Sales sheet is getting too crowded. To improve the overview, you’ll create a new sheet for the tables:

Choose Add Sheet from the Layout
On the General page, enter Tables in the Title box, then click Next>.
Click Finish.
Go back to the Sales
Move the pivot table to the new sheet
Move the straight table to the new sheet.
Go to the Tables sheet to adjust the layout.

Saving, closing and exiting

Choose Save from the File menu. QlikView saves the changes you’ve made to your document. You can now close the file:
Choose Close from the File menu.

If you won’t be working with QlikView for a while, you can also exit the program:

Choose Exit from the File menu.

3. MORE CHART TYPES

Opening the document

If you closed the document and exited QlikView after the previous lesson, you need to open it again.

Start QlikView by double-clicking the QlikView icon.
Select the file you created in the previous chapter, named MyTutorial.qvw or something similar, directly from the File menu.

Creating a line chart

You’ll create a line chart illustrating how the sales per customer have changed over the years.

Go to the Sales
Click the Create Chart button in the toolbar.
Select Line Chart, and choose Customer as Window Title.
Click Next>.
On the Dimensions page, move the fields Year and Customer to the column of displayed fields.
Click Next> to create an expression in the Edit Expression
In the Aggregation Functions and Fields group, create the expression Sum(Sales), then click Paste.
Click OK.
Click Finish. (When no values are selected, the chart looks a bit overcrowded; as soon as you make a selection, though, the trends will appear very clearly.)
Clear previous selections by clicking the Clear Selections button in the toolbar.
Select Atlantic Marketing in the list box Customer and study the result (see the figure above).
Undo your selection by clicking on the list box Customer with the right mouse button and choosing Clear from the float menu.
Select John Doe in the list box Salesman.

One now gets a clear picture of Mr. Doe’s doings. We see that he has had business contacts with Carlsborg since 1991, and that Mary Kay has meant a lot to his career so far. We also see that he wasn’t very successful with Captain Cook’s Surfing School.
Suppose you want to know if Captain Cook’s Surfing School is still our customer after all.

Deselect John Doe by clicking the value again.
Select Captain Cook’s Surfing School in the list box Customer.

No need to worry: the surfing school is still our customer, even though it purchased less during 1996 and 1997. In the pivot table that we moved to the Tables sheet you can study the exact data.

Clear your selections.

Adding an expression to a bar chart

Suppose you would like to see how area and population are related to each other.
Start by adding an expression to the existing bar chart.

Click on the chart with the right mouse button, then choose Properties from the float menu.
On the General page, change the window title to Area/Population.
Select the check box Chart title, and type Area/Population in this box too.
On the Expressions page, click Add to get to the Edit Expression
Compose Sum of Population (mio) in the Aggregation Functions and Fields group, then click Paste.
Type Population into the Label
Click OK.
Click Apply. (Drag the dialog box aside and study the chart. You have set both population and area as expressions, but only the area is shown.)
Go to the Axes
Select Population in the Expressions box, and then click Right under Position.
Click OK.

Turning the bar chart into a combo chart

In a combo chart, you can combine the features of the bar chart with those of the line chart, e.g. by showing one expression as bars and the second as lines and/or symbols.

Open the Properties
On the General page, select Combo Chart.
Go to the Expressions (The expressions Area and Population are listed in the Expressions box.)
Select Area, then mark the Bar check box under Plot Options. The boxes named Line and Symbol must not be marked.
Now select Population in the Expressions box, and then mark the check boxes Symbol and Line, leaving the check box Bar
Click OK.

Turning the combo chart into a scatter chart

Click on the combo chart with the right mouse button, and then open the Properties
On the General page, select Scatter Chart.
Click OK.

The dimension (Country) is represented by the symbols, and the expressions (Population and Area) are displayed on the axes. You immediately see that six of the countries are placed far out to the right on the x-axis, which means that their areas are far above the average. One of the countries also has a huge population.

Select those countries by “painting” the area in the chart using the mouse button.

You see that the countries are Russia, Canada, China, USA, Brazil, and Australia.

Creating a scatter chart from scratch

Click the Create Chart button in the toolbar.
Choose Population as both Window Title and Chart Title, and select the option Scatter Chart.
Choose Next>.
On the Dimensions page, move Country to the column of displayed fields.
Choose Next>.
The Expressions page of the scatter chart differs somewhat from that of the other charts. Choose Growth in the X combo box, and Population (mio) in the Y combo box.
Click Finish. (Your new scatter chart is finished. Move it, size it, and try it by making selections.)
Minimize the chart.
Clear your selections.

Creating a gauge chart

Quite often you want to view the changing value of a single measurement as you change your selections. For this purpose the gauge chart is ideal. QlikView offers a wide range of gauge charts for graphic visualization of values.

Go to the Sales
Click the Create Chart button in the toolbar.
Select Gauge Chart, and type in Gross margin as Chart Title and Window Title.
Click Next>.
On the Dimensions page, we do nothing at all, as most gauge charts are best calculated without any dimension resulting in one single value over the entire data set.
Click Next> to create an expression in the Edit Expression
In the Aggregated Functions group, create the expression Avg([Gross Margin]), then click Paste.
Click Next>
On the Style page, make sure that the first icon under Look (circular gauge) is selected.
Click Next>.
On the Presentation page, enter the value 3000 under Max in the Gauge settings
Click Finish. A semi-circular gauge with two segments, one green and one red will appear.

Let’s do a bit of analysis!

Click the Clear button in the toolbar. The gauge now shows average gross margin for all customers.
Select Atlantic Marketing in the Customer list box. This is a good customer!
Select the Asian Trade House Room for improvement!

Working with a drill-down chart

These charts can be of two types, drill-down or cyclic. In a drill-down chart, the field group defined usually consists of fields forming a natural hierarchy, e.g. Year, Quarter, Month.
The Sales sheet in your document contains a minimized drill-down chart.

Go to the Sales
Restore the minimized Drill-Down chart – found in the left-hand corner of the screen – by double-clicking it.

The chart, showing the sum of sales per year, looks like any other bar chart. However, as soon as you make a selection causing the field Year to have only one possible value, you discover its drill-down character:

Select the bar 1995 in the chart.

An ordinary chart would now display one bar, representing the sum of sales for 1995. This chart, however, shows the sum of sales for each quarter of the year 1995 (the second field in the field list defined being Quarter).

Select the bar representing the fourth quarter.

The chart turns to showing the sales for each month of the selected quarter. Month is the third, and last, field in the field group.
Note the selections in the Current Selection box in the lower right corner of the sheet. Keeping track of selections is very important when working with drill-down charts.

To go back in the hierarchy, click in the outer chart area, or on the drill-down button next to the field name.

Copying a sheet object to Clipboard

Click with the right mouse button on a chart to open the float menu.
Choose Copy Image to Clipboard.
To view the result, open a blank document in e.g. Word, and click Paste.
Close Word.

Printing

Go back to QlikView.
Click on a chart with the right mouse button, then click

The Print… dialog opens. The General page of this dialog contains the basic print options. The Layout page is used for setting margins and for deciding whether a selection stamp should be printed or not. On the Header/Footer page you may specify header and footer for the printed pages.