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.
It was a bit hard to find how to configure and use Git with Qlikview so I guess this is a good chance to start my own blog as I always have ideas that might help others out there.
I hope you find this first post useful. Please feel free to comment and ask. If I’m not able to answer, then someone else will.
To give you a bit of background, I’m working for a digital marketing company. Our BI department grew from myself to 4 people now. At the beginning it was easy to handle different projects as each person had specifics projects assigned and everything happening in a project was always resolved by the same person.
That’s not the case anymore and we now work on different projects even if they weren’t our own creation.
Leading by example and providing solutions
As responsible of the department, I decided that it was a good time to start versioning our files. In a previous project I used to version the .qvw binary file with no data. This, of course, is not the best option.
Qlikview allows us to create/recreate .qvw files from .xml files. We will then version these files rather than the .qvw file
Ready to star?!
2) Now with private key ready, right click in folder you want the repository to be in and click on “Git Create repository here…”.