Analyzing metrics in one chart
A couple of weeks ago, I was asked to create two charts. One that could show the Sales made in each of the websites where they sell their products, and one showing the average shipping cost.
Until that moment, they were visualizing these values in a plane table. I was told that I could create whatever I thought it was the best solution. Here’s my approach.
I first created a bar chart to represent Sales by Site. I got something like this:
The problem here is that the numbers in the Y axes were too big and not formatted, so I couldn’t say I was actually analyzing a metric represented by money without looking at the chart title.
After formatting a bit the chart, I decided I would add the average shipping cost in the same chart so they avoid looking at two different places. This will also help them optimize web sites where sales and shipping cost are not performing as expected.
At this point I had something like this:
Now you can clearly notice that your blue bars represent Sales which are expressed in dollars, and whatever number you see should be multiplied by 1000 (k).
You can also quickly realize that the left axes belongs to your Sales as it’s in blue, and your right axes represents the avg shipping cost as it’s in red as the dots.
Analyzing the chart in more detail, I realized that when fixing your eyes to the Sites in the middle, you must go back and forth between the left axes and Site name, otherwise you can’t tell how they are performing.
My next improvement would contain the values on the data points. I looked like this:
A lot better. I can now easily identify my KPIs, the scale, how the numbers are formatted, what they represent and now there’s no need for me to move my eyes when I’m looking at the bars as I have the Sales value right there.
But, the problem comes when I have many values on my X axes as those numbers become unreadable.
Why not formatting also those numbers on top of the bars?
This can be achieved by creating an expression that contains the formatted values. When working on this expression, only leave “Values on Data Points” checked. This will make Qlikview won’t represent this as an object (no bar, line or dots) but only as text.
The expression I used in this case was:
IF(sum(Sales)>=1000 AND sum(Sales)<1000000, money(sum(Sales)/1000,’$#,##0(k)’),
IF(sum(Sales)>=1000000, money(sum(Sales)/1000000,’$#,##0(m)’), money(Sum(Sales),’$#,##0′))
As you can see, I format the values that are greater than $1,000 to show as $1(k), and those greater than one million to show as $1(m).
Here’s the final version:
Users were able to analyze two different KPIs using one chart. They can also quickly decide what Site is performing well or bad in seconds. The different colors and shapes (Bars and dots) allow them to have a clear idea of what’s Sales and what’s average shipping cost. By coloring the axes with the same color as the KPI, they can identify which one represents Sales and which one represents average shipping cost.
*Note: this is an example that uses sample data generated for this post.