Sheets and Boxes in QlikView

Working with Sheets and Boxes in QlikView

1. SHEETS 

• Opening the document

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

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

• Presentation

The sheets will, however, still be logically connected, i.e. a selection made on one sheet will   affect all sheet objects on all other sheets.

• Logical connections between sheets

Each sheet has a tab attached to it. Containing the name of the sheet, the tab helps you find the sheet you are looking for.
There are two sheets in your document: Geography and Sales. Geography is the active sheet.

1. Click on the tab Sales.
2. Select the value Albania in the list box Country.

The cell of the selected value turns green and you immediately see all the values of all other fields that are compatible with the selection (white)

3. Go to the sheet Geography by clicking on its tab

Note that the value Albania is selected (green) in this list box too, although you made your selection on the sheet Sales.

4. Go back to the sheet Sales.
5. Select the item Cesar Sandu (currently excluded, i.e. gray) in the list box Salesman.

You immediately see that Cesar Sandu has been active in France, Germany, and Mongolia. The item Albania, which is not compatible with the selected item Cesar Sandu, has been excluded.

6. Go to the sheet Geography.

The data displayed in the sheet objects has been updated to show the result of the new selection: France, Germany and Mongolia, as well as the items related to these countries, are shown as optional.
Note the green dot on the tab Sales. This is a selection indicator, helping the user to keep track of selections made on other sheets.

• Adding a sheet

1. Choose Add Sheet from the Layout menu. A new sheet appears.
2. Click on the empty sheet with the right mouse button and choose Properties from the menu that appears. The Sheet Properties dialog will now appear.
3. Change the title from Sheet 3 to Customer.
4. Click on the Fields tab.
5. The dialog page now appearing contains a list of the available fields. Select Customer, then click the Add > button. The field has now been moved to the column of displayed fields, which means that it will appear as a list box on your sheet.
6. You can also add fields to the column of displayed fields by double-clicking them. Double-click the field name CustomerID.
7. Click OK to close the dialog

• Moving a sheet

Your new sheet Customer, containing the list boxes Customer and CustomerID, is placed on the right-hand side of the sheet Sales. Suppose you want it in the middle:

1. Click on the tab of your newly created sheet with the right mouse button. From the float menu that opens, choose Promote Tab.

The new sheet has now been placed between the sheets Geography and Sales.

2. Clear all selections by clicking the Clear Selections button in the toolbar.

• Adding sheet objects

The following sheet objects can be used in QlikView:

1. List boxes
2. Statistics boxes
3. Multi boxes
4. Table boxes
5. Charts (including pivot tables and straight tables)
6. Input boxes
7. Slider objects
8. Current Selections boxes
9. Bookmark objects
10. Buttons
11. Text objects
12. Line/Arrow objects SHEETS working with qlikview 37
13. Custom objects

All the sheet objects except buttons, text objects and line/arrow objects can be used for making selections in the data. All sheet objects may be used for viewing the result of selections.

• Saving, closing and exiting

1. Choose Save from the File menu. QlikView saves the changes you’ve made to your document. You can now close the file:
2. 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. LIST BOXES AND STATISTICS BOXES

• Opening the document

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

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

• The list box

The list box, which is the most basic object on the screen, contains a list of all the values of a specific field.

Adding a list box

Suppose you want to add a list box containing countries. The usual way of doing this is the following:

1. Make sure that the sheet Customer is active, and then choose Select Fields from the Layout menu.
2. Double-click the field Country to move it to the column of displayed fields.
3. Click OK. The field Country now appears as a list box on your sheet Customer.

Copying a list box

In case you want to add a list box that is found on another sheet, you can simply copy it. The list box Country, e.g., is found on the sheet Geography:

1. Click on the tab of the sheet Geography to make it active.
2. Press the CTRL key and keep it depressed while placing the cursor on the header of the list box Country.
3. Press the mouse button and drag the list box to the tab Customer. While dragging, make sure that a small plus sign appears; if it does not, this means that you have released the CTRL key.
4. When the cursor turns into a round arrow on the tab Customer, release the mouse button, then the CTRL key.
5. Go to the sheet Customer to make sure that the list box Country has appeared. Its position on the sheet should be the same as on the sheet from which it was copied.

If you prefer the standard Windows Copy and Paste commands, they can be used as well. You find them in the Edit menu. The standard Windows shortcuts CTRL+C and CTRL+V also work.

Moving a list box

To move a list box step by step, use CTRL+arrow. For bigger steps, use CTRL+SHIFT+arrow. To move several list boxes at the same time, start by selecting them. This is done in the following way

1. On the sheet Customer, place the mouse cursor in the bottom right corner, then press it and drag a rectangle enclosing all the list boxes on the sheet. Note that the headers of the list boxes turn blue. This means that they are selected, i.e. active.
2. Place the mouse cursor on the header of one of the list boxes, then press the mouse button and drag. All the selected list boxes are moved. It is also possible to select several list boxes by SHIFT-clicking their headers.

Sizing a list box

1. You can size list boxes (and other sheet objects) by dragging the window frame of the object.
2. Click on the header of the list box Customer (still on the sheet Customer) to make it the only active list box. If the other list boxes remain active, they will be sized as well.
3. Move the pointer to one of the corners of the list box until the appearance of the pointer changes
4. Press the mouse button and drag

Adjusting the layout

There is, however, an easier way of adjusting the layout:

1. Position the mouse cursor in the bottom right corner of the sheet, then press the mouse button and draw a rectangle on the sheet. All the list boxes within the rectangle are now selected.
2. In the Layout menu, place the mouse cursor on the Align/Distribute command. The cascade menu that opens contains several commands. Choose Adjust Left.

The list boxes are now separated by equally sized spaces. If there is not enough space between the list boxes, drag the rightmost listbox further to the right, then choose Adjust Left again.

Displaying and using the design toolbar

The design toolbar contains commands for adding sheet objects, moving sheets, and adjusting the layout. Suppose that you want to top align the list boxes of your sheet:

1. If the list boxes are not already selected, select them by choosing Activate All from the Edit menu.
2. Click the Top Align button from the design toolbar.

Removing a list box

If you have followed all the steps above, there are two Country list boxes on your sheet Customer. You only need one:

1. Click on one of the Country list boxes of the sheet Customer with the right mouse button.
2. From the float menu that appears, choose Remove
3. QlikView issues a message asking you if you are sure about removing the list box. Click Yes.

A further possibility for removing a sheet object is by pressing the DELETE key.

Minimizing and restoring

Note the symbol in the upper right corner of the chart and the table box on your Geography sheet. This symbol indicates that the sheet object can be minimized. To make a list box minimizable, do the following:

1. Click on the list box with the right mouse button and choose Properties… from the float menu.
2. Go to the Layout page and mark the check box Allow Minimize, then click OK. The minimize symbol should now have appeared in the top right corner of the list box.
3. Click the symbol or double-click on the header of the list box.

The list box turns into an icon, which is placed in the bottom left corner of the sheet. The icon can be moved freely.

4. Restore the list box by double-clicking the icon.

Changing the properties

Showing frequency
Suppose you are interested in knowing how many sales have been performed during different years.

1. Clear your selections by clicking the Clear Selections button in the toolbar.
2. Go to the sheet Sales.
3. Click on the list box Sales with the right mouse button and choose Properties… from the float menu.
4. On the General page of the List Box Properties dialog, select the Show Frequency check box by clicking in it.
5. Choose OK.

Each value in the list box is now followed by its number of occurrences. We can e.g. see that 14 sales for $900 have been made.

Changing the number and order of columns

To display the contents of a list box in several columns, do the following:

1. Clear your selections.
2. Click on the list box Day with the right mouse button, then choose Properties… from the float menu.
3. Go to the Presentation page.
4. Deselect Single Column, then click OK.
5. Drag the border of the list box Day until its contents are displayed in several columns

The values are ordered by column, i.e. vertically. You may prefer to have the values of the Day list box ordered by row:

6. Click on the list box Day with the right mouse button, then choose Properties… from the float menu.
7. Go to the Presentation page.
8. Deselect Order by Column, then click OK.

The field values, instead of being ordered by column (vertically), are now ordered by row (horizontally).

Changing the sort order

In addition, list boxes whose values are not all visible (list boxes with scroll bars) are set to Sort by State, which means that the values are sorted according to their logical state (selected, optional, excluded).

1. Click on the list box Sales with the right mouse button, and choose Properties.

Choose the Sort page by clicking on its tab.

1. Keep the option Numeric value selected, but change the order to Descending by choosing it from the drop-down box (click the arrow to the right)
2. Click OK.

The highest number is now at the top. As soon as a selection is made, however, the selected (green) value(s) or optional (white) values will be placed at the top.

3. Make a selection in the list box and study the result.
4. Clear your selections.

Changing the number format

1. Click on the Sales list box with the right mouse button, and choose Properties…, Number.
2. The number format of the field Sales is disabled because all number formats are inherited from the documents by default. To set the number format for the Sales list box in US dollars do the following:
3. Click in the Override Document Settings check box in order to create a separate number format for this list box.
4. Select the option Money, then click OK. Note that the values in the list box Sales are now differently formatted (you may need to size it first): a comma has appeared as thousands separator and the values are preceded by a $. Two decimals have been added.
5. Open the Properties dialog again. The dialog page on which the number format is set LIST BOXES AND STATISTICS BOXES working with qlikview 49 5 Study the Number dialog page. The current format is displayed in the Format box, and below this a preview is given. The format can be changed manually. Erase the two decimals (the zeros) and view the result of the change in the Preview box.
6. Click OK to close the dialog.

Aligning the values

Text is usually left-aligned, numbers right-aligned. This setting can be changed on the Presentation page.

1. Click on the list box Year with the right mouse button, then choose Properties… from the float menu. Go to the Presentation page.
2. In the Alignment group, click Left for numbers.
3. Click OK.

Changing the border

Every sheet object has a border that can be given a number of different appearances.

1. Click on a list box with the right mouse button, and choose Properties…, Layout.
2. Pick a border format of your choice
3. Click OK

Changing the font

To change the font of a single object, open the Font page in the dialog of the particular object. To change the font of the entire document, open the Font page of the Document Properties dialog.

The statistics box

The statistics box is a compact way of showing a numeric field in which the separate records are not interesting, but e.g. the sum or average is. A number of different statistical functions can be used in a statistics box. It is also possible to make selections in the statistics box by clicking on some of the functions, e.g. Min, Max, Median, etc.

Creating a statistics box

1. Make sure that no selection is made by clicking the Clear Selections button in the toolbar.
2. Make the list box Sales on the Sales sheet active by clicking on its header.
3. Click on the list box with the right mouse button, and choose Create statistics box from the float menu.

Another possibility is to choose New Sheet Object, Statistics Box from the Layout menu or to click the Create Statistics Box button in the toolbar. In that case, the Properties dialog of the statistics box is opened. This dialog looks similar to that of the list box, but only contains three pages. On the General page, you select the statistical functions you wish to use.

Making selections in a statistics box

You can make selections in a statistics box by clicking on the non calculated functions, e.g. Min, Max, and Median.

1. Click the function Max to find the customer who made the purchase. The selection is made in the list box to which the statistical value belongs.
2. Clear all selections by clicking the Clear Selections button in the toolbar.