
Dashboard redesign in Qlik Sense
In this tutorial you will learn about the creation of a dashboard in Qlik Sense. To be able to develop this dashboard we advice you to download the necessary QlikView 11 file, it can be found at: http://qlikshow.com/Exercise%20app.qvw. This app will be used to load the data from for our design efforts in Qlik Sense.
Qlik Sense can be downloaded from: http://www.qlik.com/us/explore/products/free-download
Preface
Now, this dashboard redesign as layed out could not have been build if it wasn’t for another Qlik enthusiast from Atlanta, GA. His name is Speros Kokenes, to my opinion he really understands what to do with QlikView and Qlik Sense. I would call him an addict, who has a quite unique set of skills (Javascript, CSS, HTML, Qlik development and an understanding of business principles). You can find his blog at: http://blog.axc.net/
The idea for this post was a joint effort between the two of us. This redesign had to include the grid chart as we know it in QlikView 11. This chart which takes two dimensions and one measure can be found at bullet 1. Since it does not exist in Qlik Sense, Speros’ idea was to create one for this design. Very good idea, our community will be grateful I guess.
Now lets get started…….
Introduction
Qlik Sense provides a very easy way of creating your dashboard applications. To experience this for yourself this tutorial will help you to see how it is done.
The dashboard we are going to create is a redesign of a dashboard built in QlikView 11. This dashboard we are redesigning in Qlik Sense can be found at: http://qlikshow.com/steps-in-designing-a-dashboard/
What can you expect to achieve?
This tutorial will help us to achieve the dashboard that you see here above.
What are we redesigning?
This is a previous design of this same dashboard built in QlikView 11.
Loading the data from the downloaded app (Exercise app.qvw)
1. Open Qlik Sense
2. Create a new app
3. Open the ‘Data load editor’
4. Add the following line on line 1 of the script: (as seen in the example above)
Binary [C:\Exercise app.qvw]; (Since this isn’t working anymore in newer releases, this now has to be:)
4. On the right hand side open the connections editor. Here you want to point to your folder where you copied the exercise app to. Your binary load statement after this should look something like this:
Binary 'lib://Data/Exercise app.qvw';
Save your adjustments and press ‘Load data’
After the execution is finished you will have loaded the necessary data into your Qlik Sense app.
Next we also need to add 3 color variables for later on in the tutorial. Add the following to the script:
SET cGood = ‘=rgb(145,180,50)’;
SET cMedium = ‘=rgb(249,231,6)’;
SET cBad = ‘=rgb(230,51,26)’;
Starting with our design efforts
First we have to add a new sheet to our application. For all the steps that are not detailed enough, I know just the resource for that: http://help.qlik.com/
Now we have done that, it is time to start adding some visualizations and objects to our sheet which you probable named “Dashboard”.
Starting with our KPI tiles
To create the above, do:
1. On the sheet press ‘Edit’
2. Add a ‘Text and image’ object to the top left of the screen
3. Adjust the size to 1*2 cells
4. Add the text ‘Orders’ to it
5. For this tile to show a number, we need to add an expression to the object. Create an expression for ‘Orders‘:
count(DISTINCTOrder_ID)
Best practice is to add these expressions (measures) to the master items (repository): Create master measures
I advice you to do this with all measures, these will be re-usable and maintanance will be done centrally.
So first create all your master items with the following measures:
Revenue:
sum(SalesPrice*Quantity)
Gross margin %:
(sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity))/sum(SalesPrice*Quantity)
Profit:
(sum(SalesPrice*Quantity)
–
sum((CostPrice)*Quantity)
–
sum((SalesPrice*Quantity)*FixedCostFactor))
When this is done, create all the text objects with the corresponding measures as seen above.
Creating a horizontal hairline as a section divider
This hairline will help your users to distinguish the measure summary at the top from the rest of the charts and sections.
To add this design element, do the following:
1. Add a new text object to the canvas
2. In the object properties on the right go to Appearance > Background image > Select an image
3. Add ‘Hairline.png’
4. Position the image. Choose to vertically and horizontally center the image.
Adding our first chart: Revenue trending over time
To create this chart do the following:
1. Drag a barchart on the canvas
2. Add two dimensions: Year and Quarter
3. Add ‘Revenue’ as the measure, choose this measure from your master items as created earlier
4. Change the sorting order of the year to ‘Descending’. This because we want to show the most recent year first.
5. Position this chart under the measure summary at the top. An example of all positioning and alignment can be found in the first design image in this tutorial.
6. To create a coherent design add another hairline underneath this chart, exactly the same object as in the previous step
Our result now should look like this…
Adding the scatterplot for insight into distribution of customers over revenue and gross margin %
To add this chart do the following:
1. Drop a scatterplot in the right top corner on the canvas
2. Choose ‘Customer’ as the dimension
3. Add 2 measures: Revenue and gross margin % (Choose from your master items)
4. You could choose to adjust the appearance settings of the axes to have a narrow scale if you like.
5. Add a title to the chart: “Distribution of customers between revenue and margin %”
6. The chart should cover 7 * 13 cells on the responsive canvas in the right top (Next to the measure summary leave a 1 cell whitespace between the scatterplot and the measures)
Because we want to make this chart more intuitive we could add some colors based on the performance outcomes. We therefor use the colormix function. The following expression can be added under Appearance > Colors and legend > Choose ‘Color by expression’. In the expression editor add:
ColorMix1 ((1+Sign(2*((sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity)-RangeMin (top(total (sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity),1,NoOfRows(total))))/(RangeMax (top(total (sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity),1,NoOfRows(total)))-RangeMin (top(total (sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity),1,NoOfRows(total))))-1)*Sqrt(Fabs((2*((sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity)-RangeMin (top(total (sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity),1,NoOfRows(total))))/(RangeMax (top(total (sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity),1,NoOfRows(total)))-RangeMin (top(total (sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity),1,NoOfRows(total))))-1))))/2,
cBad, cGood)
Note: I cheated with this expression by creating it in QlikView 11. But you can just copy and paste it in your chart. 😉
Last thing we need to do here is to add a hairline below the scatterplot to fit the design as shown in the example dashboard.
Our results now look like…
The red arrow shows an addition to the dashboard which we did not cover yet. Next thing is to add a vertical hairline as shown in this example.
Adding a treemap for comparison of productgroups
Now, a treemap is not your best choice for comparing productgroups with eachother. But in this design we will use this chart anyway 😉
To add this chart we have to do the following:
1. Drag and drop a treemap in the right bottom corner of the canvas right below the hairline
2. Add ‘ProductGroup’ as the dimension
3. Add ‘Revenue’ as the measure in this chart
4. Add a title: “Comparison of productgroups”
5. Under Appearance > Colors add, again the expression for coloring based on the performance of gross margin %:
ColorMix1 ((1+Sign(2*((sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity)-RangeMin (top(total (sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity),1,NoOfRows(total))))/(RangeMax (top(total (sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity),1,NoOfRows(total)))-RangeMin (top(total (sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity),1,NoOfRows(total))))-1)*Sqrt(Fabs((2*((sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity)-RangeMin (top(total (sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity),1,NoOfRows(total))))/(RangeMax (top(total (sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity),1,NoOfRows(total)))-RangeMin (top(total (sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity),1,NoOfRows(total))))-1))))/2,
cBad, cGood)
Have a close look at the chart in this steps’ example. Finish adding the missing elements that make your chart exactly the same as this one.
Adding a gridchart to the canvas, huh?
What??? Gridcharts aren’t part of the Qlik Sense chart library, right? Yes, you are right. But… I co-developed this dashboard together with Speros Kokenes. He developed a new extension for this dashboard redesign.
The extension can be downloaded here: https://github.com/skokenes/Qlik_Sense_Extension-GridChart
So after you downloaded the extension, you’ll have to unzip it in the folder: C:\Users\<USERNAME>\Documents\Qlik\Sense\Extensions
After that we are set. Now lets add the chart to the left bottom of our user interface (UI). To get the same results as shown above, do the following:
1. Add two dimensions to the chart: Sales Manager and Product Category
2. Add ‘Revenue’ as the measure
3. This gridchart can work with colors based on an expression, but the expression has to be the 2nd expression in the chart in which you define the rules for the calculated color
4. So in the measure pane we click on ‘Add measure’ and then we again add the expression we used before for calculating colors based on the performance of gross margin %
5. Last thing we want to do is add a title to the chart: “Product group sales per Sales rep”
Note: There is also an option under ‘Appearance’ that says ‘Color’. This can only be used for a fixed solid color in Hexadecimal format. (Like: #000000 for black)
After adding the previous step we have our final results
If you have any questions about this tutorial just give us a call at:
Patrick Tehubijuluw: https://twitter.com/Creatuluw
Speros kokenes: https://twitter.com/speros_axis
Have fun in the meantime!
Patrick,
The URL of the excercise file is not correct (http://qlikshow.com/Qlik/Exercise)
If you copy the text, it is working http://qlikshow.com/Qlik/Exercise app.qvw
Thanks. Fixed…
Hi, the file at http://qlikshow.com/Qlik/Exercise app.qvw does not exist. Thx
I fixed it now. Thanks for your quick response to this.
Great stuff Patrick!
Just a minor caveat to your approach. One of Qlik Sense’s primary features is supporting “responsive” design so that visualizations can be automatically displayed effectively on any screen be that a laptop or any of the various mobile devices. Putting graphics such as the dividing lines you use in your example could cause that redisplay to have unanticipated consequences when the display changes to a different sized screen. It will work, but those lines could get in the way of a display of the dashboard going from a laptop sized screen to an iPhone for example.
This may or may not be an issue for a particular dashboard scenario (maybe mobile devices will never be used to access the dashboard), but just keep in mind that trying to maintain a “pixel perfect” UI design coming out of QlikView may not be appropriate for Qlik Sense.
Peter, you are completely right. This post is only for those who wonder what can be done when comparing to QV 11.
When I try to load Exercice app.qvw. I receive an error.
Cannot open file ‘C:\Temp\Exercise.qvw
Set ThousandSep=’.”
Binary [C:\Temp\Exercise app.qvw]
SET ThousandSep=’.’
Data Load failed
Sorry, I forgot ; on my script!
Frédéric, you will have to put up the binary load statement on line 1 of your script. Did you try that already?
I am getting the same file does not exist error. Thanks!
What link are you using. I should have fixed it after the first feedback came in…
I tried to fix problem but still get error
Binary [lib://C:/DataQS/Exercise app.qvw];
Loading data starts, but i get error loading data not succeeded
What am I doing wrong
Did you solve it already? Let me know.
Hi,
I´m getting the same error message!
Binary [lib://C:/DataQS/Exercise app.qvw];
Loading data starts, but i get error loading data not succeeded
What am I doing wrong
Havent solve it 🙁
Luis, from here it looks like if you didn’t specify a connection folder yet. If you do so, your line of code for loading a file should look like this: Binary
'lib://Data/Exercise app.qvw';
.You could add or change a connection on the right hand side in the data load editor. Let me know.
I am new here, can not load using latest version Qlik Sense Desktop – Binary [C:\Exercise app.qvw];
Can you help with modified steps since this may have been disabled in latest release. Thanks
Did you solve this already?
Jignesh, you now should try adding a new data connection to the file. This can be done by opening the ‘data load’ editor and at the right hand side add the folder in which you copied your exercise.qvw app. Let me know if this worked for you. I also adjusted the tutorial for this minor bug. Seems this changed in Qlik Sense during the latest releases. Thanks anyway.
Hi Patrick,
Thanks for the great tutorial.
Sorry I’m new to qlik sense how did you get the color to green and red I only get orange or many different color onto the charts.
I used the colormix function for that one.
What you mean with colormix Funktion? Do you Know if there is somewhere a Tutorial for Color formulas (how to use) And formulas which are useful for data load editor?
Andreas, I put up a new post with some tips around working with this function. It can be found here: http://qlikshow.com/information-design-tips-on-qlik-sense/. Does that help you?
Great article!!
Hi Patrick,
Do you have any solution for changing the font style in Qlik Sense?
Mariia,
You could try it with a CSS style sheet…
Hello this link to download the data is still not working.
It does now. Let me know…
Thank you. Works a treat.
Hi,
I am new to Qlik sense, I could see here the expression wise color changing, But what is the way to change the colors ByDimension like persistant colors we can change in qlikview but Is there chance to change in Qlik sense?
Hi Kumar, I think for now you have to calculate your way thru coloring. Hopefully this will change…
Hi Patrick,
Good stuff here, the link to the exercise still does not work though.. Could you update?
Thanks a lot..
Thanks for the feedback. I did an adjustment to the link. Let me know…
Most qlik extensions from Qlik branch fail when uploaded to Qlik server. Any ideas for work around?
Try to contact the original developer on tips and tricks for that.
Dears,
Is it possible to add Colour in Pivot table dimension Qliksense
Not yet I guess if I understand what you mean.