Create Dials and Speedometer Scorecard
August 19, 2010 – 2:59 pmIn this article we will take an Excel source containing scorecard information and convert into dials and speedometer charts.
We will also create few bar trending charts.
Consider the below sample data
Source File: You can download the excel file here
The above excel file tracks few metrics such as safety, quality and revenue. This is just a sample data and may not make sense in actual world but just imagine a manufacturing company that wants to track its overall safety and quality score and also track its total revenue. Now the company may use its own method at deriving the individual safety and quality scores.
They may have a dedicated team to collect safety violations and product defects and then use some formulae to convert those data into final score for any given month. We are not concerned with any of the methods on how those scores are generated. You are the dashboard person and the company has provided you the metrics by each month and now your job is produce a nice dashboard that shows relevant charts for the given data.
Since the data is tracked for each month, it makes sense to show a line or bar chart trending for the metrics.
Dials and Speedometers are relevant when we need to show performance of a single value. So in this case, since we have 12 month data, how do we show single value on the meter chart?
One solution is to show an ‘Average’ value for the safety and Quality metrics or we could show a dial that shows the current month values.
So the GOAL for this exercise is: Show bar chart for Safety trending 12 months and show average and current month value on the dial chart.
Dial chart requirement: For the dial chart, we are measuring the performance, whether we are on track or missed the goal. In order to achieve that, we create three new columns for safety such as ’safety_bad’, ’safety_ok’, safety_good’. These values need to be decided by the company management. If you are not sure, then look at previous year data and decide what should be good and bad values. The dials are used to guide the company to perform better or atleast improve that portion of the company operation represented by the metric.
First let us create the date hierarchy columns from the Period column. You need to make sure that the Period column is an actual Excel date column, else the hierarchy will not be generated
Right click on the period column, select Create -> Add Year, Quarter, Month columns
As you see we added the year and month columns. We may not use all of the above columns but having them ready gives us the option during the chart building.
Now right click on the “Name” and select ‘Create Chart”
We selected the ‘Bar’ chart type
For the x-axis we select the month column and for the y – axis we select the Safety metric
Next, we click on the “Create Chart” button
This action takes us to the below dialog
We rename the chart to ‘Safety’ and then click on ‘Fully Apply’
This action create the below chart (You may need to move the screens and dialog around to see the chart behind)
Now having the chart wizard open, we change the selection to ‘Dial’ chart
Check the Dial chart options below
We modify the options as below
- You can type the low and high range values for each range on the dial.
- In our case, we have defined the safety bad, ok and good values in our spreadsheet so when our goals change, we just change the spreadsheet and the dials will automatically take care. Also, what if we needed different goals for each month so rather than hard coding the range in the chart, we get them from the excel file
We first change the label sequence for the “Region Name” from “Bad” -> “Warning” -> to “Good”. For the dial chart the labels are not used so make sure that the colors are selected appropriately.
You may click on the color icon to change the colors of your choice
Next, we select the range boundary values
The safety_bad value from our spreadsheet represents the lower boundary of our bad region, “safety_ok” represents the upper boundary of our bad region, similarly ’safety_ok’ is the lower bound for our ‘ok’ region and so on. For the ‘Good’ upper bound we simply typed a value but as general practice we should have one more column for the upper bound of the final region. In our case the final region is ‘Safety Good’.
Next, for the pointer value, select the column that you want to show on the dial chart. In our case we select ‘Safety’ and aggregation as ‘Avg’
Next, we click on “Create chart”. This action shows the dialog below
We change the chart title to ‘Average Safety’
NOTE: remove the double quotes in the SQL statement for the group by. This will cause an error.
Click on ‘Fully Apply’
Here is our final Dial Chart
Next, create a dial chart to show current month value
We will change some conditions on the main Qlet. Right click on the “Name” and select ‘Duplicate’
It creates an exact copy of the qlet table. We change the name to ‘Current Month’
Right click on the ‘Period’ column and select ‘Create’ -> Create Date Filter
This action shows a dialog as shown below
First change the operator to ‘Between’
Next select ‘First Day of Month’ for the from and ‘Last Day of Month’ for the To val.
Click on ‘Use This Filter’
This action applies the filter to the Qlet and it shows only currrent month row
Next, right click on ‘Current Month’ and select ‘Create Chart’
All of the settings remain the same except the ‘Aggregation’, make sure to select ‘NONE’
Once all the three charts are done, we arrange them as below
Similarly, you may create the charts for “Quality” and “Revenue”
