Lets Get Creative!
- Editing Mode and Basic Sheet Formatting
- Creating Your First Table
- Editing Your First Table
- Turning a Table into a Chart
- Hot Swap Between a Table and a Chart
- Multiple Charts in One (Trellis charts)
- Moving PARTS of a chart around, the hidden secret!
- Search Criteria / Selection Menus
Some basic setup
First thing is first, change your editing mode to Advanced:
On the main menu go to: Settings > User Preferences > Design Tab and change the ‘Default Styling Mode’ from simplified to advanced.
You wont notice any changes to start with but you won’t be missing lots of options when you start to edit and create objects.
So now we have some basic data in place, but a blank, grey sheet!
The next thing, do you really like that bleak grey background? I know I don’t! Therefore right click on the tab which says main and select sheet properties.
On the genral tab you can set the background colour, or image as you see fit. Note that you can also set this up as a document default under Settings > Document properties, but I have found this to be unreliable!
Creating Your First Table
The first thing to note is that we won’t be setting this up as a table, but actually a chart!
This is because the basic tables in Qlikview are just that, they’re only good for listing data in a straightforward format really.
So right click anywhere on the screen and choose new sheet object > chart
Once again this will give you a nice step by step guide to creating your chart / table.
In this case, give the chart a title, for example: Customer Sales and choose the bottom right hand icon which is a straight table
Click on next and you will be asked to pick the ‘dimension(s)’ of the table, i.e. the piece of data we want to report against, in this case choose customer
Again click next, and you’ll be presented with the standard Qlikview formula entry box. For this example, simply enter =sum(sales_amount). Note that in this editor, the field names are case sensitive.
You can also look up the fields (in case you forget what you called them) from the fields drop down box. You can also explore the functions that Qlikview has built in on the functions tab
Hit ok on this box and it will take you back to the expressions window. You can give the formula a label here, otherwise it just shows the formula in the table header:
Now we can just hit finish as we can access the other elements of the set up at a later date. You should be presented with a table as such:
To move the table, grab the ‘caption’ bar, that is the header bar, and drag the table. You will get a hand marker showing you where the table will ‘land’.
And hey presto, your first piece of of qlikview data displayed!
Editing Your First Table
So now we have a table, how can we change it? Its actually really simple, just right click on it and go to ‘properties’ and you’ll be shown a screen with different tabs. These tabs actually relate to those
same steps in the set up. In the first case we will just make it look a little better, so go to the ‘layout’ tab. Here we can change the border to be black, and also give it a little shadow to make it stand out.
Because its such a simple piece of data, we also don’t want people to be able to print or export the data. We also want to make sure the table can’t be minimised or maximised so we will go to the caption tab and un-check these
items from the list, and screen and then hit apply. This gives us a much cleaner looking table.
Now, lets add another column to our table, this time the Sales Quantity and also the additional dimension, customer name.
Right click on the table and again go to properties, this time go to the dimensions tab and copy the customer name over, you can double click it or use the add> button
Then go to the Expressions tab and add the expression:
and also give it a sensible name:
Finally lets rename the customer name column, which is again done on the dimensions tab, and sort in order of sales amount which is done on the sort tab.
Promote the sales_amount to the top and sort on numeric value.
We can then ‘drag’ the columns on the table to be a more sensible width, hover over the dividing line until you see the black line with arrows which will allow you to do the resizing. NOTE! The last column can be a bit funny,
when you hold over the edge of the table you get the standard resizing arrows. Come slightly inside those to find the marker for the last column!:
Turning a Table into a Chart
I find it easier to create the data table first, as we have done, and then turn this into a chart. That way you can see the data before converting it.
In this case, we will slightly alter the table before converting it. We’re just going to make a few amendments to make a more sensible chart.
Remove the customer dimension, and add the sales_month dimension.
Now, ‘hide’ the sales qty expression (as we will use it later) by unchecking the ‘enable’ checkbox
Finally, promote the sales month dimension on the sort tab:
You should now have a data table which looks something like this!
Now, here is the clever bit, while holding ctrl click on the ‘caption’ (header) and drag the table to the right of itself. When you let go you should find you have two copies of the table. NOTE! If this doesn’t work
check that the copy / clone checkbox on the layout tab of the table properties hasn’t been unchecked somehow
Right click on the right hand table and go to properties, then the General tab. Simply choose the line chart button and then hit OK.
You should see a very cluttered chart!
Drag the right hand side out so it looks a bit more sensible
Now note that the dimensions are backwards, we actually would prefer that the months be on the X axis, with a line for each customer as this gives us a much better visual comparison.
To sort this, simply re-order the dimensions in the properties and then OK it
And congratulations, you have your first chart!
Hot Swap Between a Table and a Chart
So we now have a data table and a chart, but actually we are wasting space. Users will use the chart as a quick visual aid, but it may be that only on occasion do they want to see the data.
In this case, we can enable the ‘fast change’ button on the chart. This is done on the general tab on the chart properties.
We will check the ‘Line Chart’ and ‘Straight Table’ options and then hit OK. Leave the position as ‘In Caption’.
You should now see a new Icon in the top right of the chart caption. Click this and see what happens!
We can now delete the ‘spare table’ that we have (Right click > remove).
Multiple Charts by Dimension: Trellis Charts
If you remember, we kept our sales quantity expression as I said we would use it later. In this case, we want to display the amount and quantity on the same chart.
Open up out chart properties and change it to a ‘combo’ chart . Also go back to the expressions tab and re-enable the sales quantity expression.
While you’re enabling the expression, also change its type in the display options to ‘bar’ and you should see that the symbol changes.
What you should end up with is, well, a mess!!
We have all the data that we want to show, but the chart is really utter nonsense!
Go back into the chart properties and on the Dimensions tab, click on the Trellis button at the very bottom. This should pop up a box, on which you can simply check the ‘Enable Trellis Chart’ checkbox and then
OK out of all the screens.
This will give you a chart which looks like this:
Again, the data is there but its not quite right. We wanted a chart for each customer, not for each month!
To resolve this, once again re-order the dimensions in the chart properies, promoting customer_name over sales_month
This gives a better looking chart
Moving parts of a chart around
This is probably one of the most backwards things in Qlikview.
You can move elements around inside a chart, but the way of doing it is odd and somewhat hidden! But once you know how it is easy.
Firstly, undo the last changes we made so hide the quantity expresson and uncheck the Trellis option.
Make sure your chart is ‘active’ by clicking on the Caption bar (header)
Then hold down ctrl+shift. You should see the different elements of the chart now have red borders
Now just drag them around until you’re happy with their location!!!
Search Criteria / Selection Menus
A key part of qlikview is that anything is clickable, and hence selectable.
However, sometimes its really convenient to add a simple selections menu to a dashboard for users to simply choose items from.
The easiest method is the ‘Multi box’.
Right click on a blank part of the screen, and choose New sheet object > multibox.
Choose Customer, customer name, customer rep and sales month from the list.
This will give you a selections box as below:
To make selections, simply click on the appropriate row and select what you want! Below are a few points about making selections.
- To make multiple selections you can: Click and Drag, Hold ctrl and Click
- If you right click you can select excluded (the inverse), select all, clear that criteria, clear all critera
- If you click on a selected item it will un-select it
- As you make selections, you will see the other fields start to select themselves
- In the above situation, if you want to see all the other options, click the little arrow. Greyed out items are not applicable based on prior selections
- To search a field, click it and just start typing, a search box will be presented automatically
Something else which I find very useful to include is a current selections box. Although you do get the ‘green dot’ where some selections have been made, its not always obvious.
This is as simple as right click on a blank part of the screen, and choose current selections box. In this one you can simply click on the eraser button to remove the criteria
You can also create slightly niftier selection boxes using the table or list boxes.
In this case, add a list box based on the sales_month column.
Now we are going to make a set of very specific changes to this box.
- Firstly, line the top up with the top of the current selections box (assuming it is in the top left)
- Now go into the properties and:
- Change the sort to be purely on ascending numeric value
- On the presentation tab, un-check the single column box AND select center alignment for the text and numbers
- On the caption tab, uncheck the show caption box AND change the height to be 25
You should now have a very simple selection box for the months!
Note, if you then need to move this around you can either change the X/Y values in the properties on the captions tab, or just re-enable the caption to drag it around.
It does look somewhat better with month names in all honesty, but this will suffice for our simple example. Below is how it looks using years and month names