Archive | October 2013

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.



Using Git with Qlikview to version control your projects.

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?!

Installing GIT
1) Download and install Git
2) Download and install TortoiseGit
Setting up Qlikview XML files
1) For each dashboard (.qvw file) create a folder with the same name as the QV file and add the suffix -prj. For example, if your application is called “MyApplication.qvw” create a folder in the same path where the application is, and call it MyApplication-prj.
2) Open the application, save it and close it. This should create xml files in the folder create in Step 1.
We are now ready to start versioning our applications.
Setting up an account
You will need a place to host your repository. I use GitHub which is paid, but you can also use bitbucket which is free.
Creating a local repository
1) Make sure you have a private key to access the repository (this only need to happen once). If you don’t have a private key, then:
    – Start -> All Programs -> TortoiseGit -> PuttyGen
    – Click on “Generate” to generate a new random key.
    – You will then see your Public key. This key needs to be inserted in your Git profile. Go to your Git account (GitHub, Bitbuket, or the one you use) . Go to your Profile Settings and locate “SSH Keys”. Paste your public key here.
    – Go  back to Putty and Save your private key to any local directory (Ex: C:\MyKey). Make sure you don’t move or delete this folder.

2) Now with private key ready, right click in folder you want the repository to be in and click on “Git Create repository here…”.

Now you have a local folder for your repository. This repository needs to contain the -prj folder and .qvw file. You can even create your repository in your development folders.
Committing your changes
There are 3 stages when committing changes.
a) Committing to a local branch. This is stored locally and if something happens to your computer, changes will be lost.
b) Push to a remote branch. All changes will be committed to a branch in the server.
c) Push to Master branch. The Master branch is that with the working copies. You can commit several changes to your remote branch before you commit to the master (and final version) repository.
To commit a file, right click, “Git Commit”. There you’ll have a the option to create your local branch.
At this point everything is committed to your local branch. We can now click on “Push” to commit it to our remote branch. (We are still not changing the master repository, but putting everything in the server in case anything happens with our computer).
We choose the local branch previously created and enter a name for the new branch in our server. Click ok.
No everything is in our remote branch. Before committing everything to our Master repository we can keep committing things to our remote branch. Once we are ready, we right click on our Repository folder, go to “Git Branch” and change it to “master”. That means that we will now be committing things directly to our master branch in the server. We can follow the same steps as before.
Now that our changes are in the master repository, the rest of the team can Pull everything to make sure they are working with the last version.
Recreating the .qvw application from .xml files
Let’s suppose we lose our local qvw application and xml files. We have everything in our remote repository. We first Pull everything from our repo. We will have our MyApplication-prj folder. We then:
1) Open Qlikview Desktop. Create a blank document and save it under the same path of MyApplication-prj (not inside this folder!). Do NOT name it the same as the report. Put any other name.
At this stage you should see 1 folder and 1 file:
2) Open Windows Explorer and press F2 on the .qvw to manually change the name to match the application name. (MyApplication.qvw). You should now have:
3) FROM WITHIN QLIKVIEW (do not double click on the file), go to File -> Open, and open the application. Qlikview will recreate the application based on your xml files.
Version control is very useful when working in big projects or when different people are working on the same file. Implement it only if it’s worth the trouble.
I hope you enjoy my post!