Dashboard redesign in Qlik Sense

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

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

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)’;

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”.

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.

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.

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

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.

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.

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.

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)

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!