Dashboard redesign in Qlik Sense

Intro

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?

media_1408739519218.png

This tutorial will help us to achieve the dashboard that you see here above.

What are we redesigning?

media_1408739623253.png

This is a previous design of this same dashboard built in QlikView 11.

Loading the data from the downloaded app (Exercise app.qvw)

media_1408740379636.png

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

media_1408740987598.png

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

media_1408741216994.png

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

media_1409900712893.png

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

media_1409901343510.png

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…

media_1409901792085.png

Adding the scatterplot for insight into distribution of customers over revenue and gross margin %

media_1409901933500.png

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…

media_1409904596744.png

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

media_1409904798357.png

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?

media_1409905390834.png

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

media_1409905477938.png

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!