One of the essential components of a Qlik Sense dashboard is the Key Performance Indicators or the KPIs. The KPIs indicate the health of the company based on specific measures. The information displayed in the KPI should stand out distinctly and demand attention. For example, one of the key KPIs that a CEO of the company may like to have on his dashboard is "Actuals vs Budget". A CEO is mostly interested in knowing if the company is below or above the budgeted figures. So, it makes sense to highlight the required information inside the visualization object. The following recipe explains and shows you how to do this in a bar chart.
A "Dial Gauge" is quite commonly used to display the key KPIs in Qlik Sense. However, the best design practices say that the "Bar chart" is the most effective way of conveying the information to the user. The following example makes use of a bar chart to strengthen this thought.
Perform the following steps to get started:
- Create a new Qlik Sense application. Name it
Performance Measure_Bar Chart. - Open the data load editor.
- Load the following script, which contains information on the
ActualsandBudgetfor four products. The script can be downloaded from the Packt Publishing website:Products: LOAD * INLINE [ Product, Actuals, Budget Footwear, 100000, 120000 Tyres, 180000, 150000 Mountain Bikes, 250000, 195000 Road Bikes, 200000, 225000 ];
The following steps highlight the performance measure in a bar chart:
- Open the App overview and create a new sheet.
- Create a bar chart on the sheet.
- Add Product as the first dimension.
- Under the Properties panel present on the right-hand side. Click on the
dropdown menu and select Dimension. - Open the expression editor by clicking on
. - Add the following calculation as the second dimension and name it as
Performance Type:=ValueList ('Actuals Up To Budget','Actuals Below Budget','Actuals Above Budget') - Click on again and add the following measure to the object and call it as
Performance:if(ValueList ('Actuals Up To Budget','Actuals Below Budget','Actuals Above Budget')='Actuals Up To Budget', RangeMin(Sum(Budget),Sum(Actuals)))/Sum(Budget) , if(ValueList ('Actuals Up To Budget','Actuals Below Budget','Actuals Above Budget')='Actuals Below Budget' , num((RangeMax(Sum(Budget)- Sum(Actuals),0))/Sum(Budget),'$#,##0.00;-$#,##0.00') , (RangeMax(Sum(Actuals)-Sum(Budget),0))/Sum(Budget) ) ) - Once we define the Performance measure, we will notice that just below the Expression box for the measure, we get a dropdown for number formatting. Under this dropdown, change the number format to Number. Next, we define the exact format of the number. To do this, switch off Custom Formatting and then under the dropdown below that select Formatting representation as 12%.
- Under Appearance, click on General and add the
Product Performancetitle. - Under Sorting, set the sort-order for Performance Type as
alphabeticallyandDescending. - Under Appearance, click on Presentation and pick the style for the chart as stacked and Horizontal.
- Under Colors and Legend, switch off Auto Colors to activate custom colors.
- Along with the custom colors, a dropdown to define the colors is also activated. This is situated right below the colors switch. Under this dropdown select By expression.
- Add the following expression under the color expression:
if(ValueList('Actuals Up To Budget','Actuals Below Budget','Actuals Above Budget')='Actuals Up To Budget',rgb(234,234,234), if(ValueList('Actuals Up To Budget','Actuals Below Budget','Actuals Above Budget')='Actuals Below Budget',rgb(255,0,0),rgb(0,255,0) ) ) - Make sure that The expression is a color code is checked.
- The resulting chart will look like the following screenshot:
The chart in this recipe shows the user the relative performance of each product. The colored segments highlight the extent by which a product has exceeded or failed to reach the budgeted value. The Green segment indicates that the product has fared well while the Red segment indicates that the product is below the budgeted figure.
The preceding example makes use of the ValueList function in both dimension and measure. For dimension, this results in three string values, namely "Actuals Up To Budget", "Actuals Below Budget", and "Actuals Above Budget" as row labels, which are further referenced in the measure.
The measure takes the values from the dimension and references them in a nested If statement as an input to three aggregated calculations.
We use the ValueList function in this recipe as Qlik Sense doesn't allow you to have custom colors for each measure, which we needed in order to do the highlighting.
The same information can be conveyed using a CapVentis Redmond Pie Gauge, the credit for which goes to Stephen Redmond, former CTO of Capventis. The Redmond Pie Gauge chart can be accessed on Qlik Branch at http://branch.qlik.com/projects/showthread.php?159-CapVentis-Redmond-Pie-Gauge-for-Qlik-Sense&highlight=redmond+pie+gauge.