QlikTips
- <h3>Saturday, 19 May 2012</h3>Good use of pie charts?<br>FlowingData, one of the people that I follow on twitter, has blogged today about how the visualization below, published by the Wall Street Journal, was a good use of pie charts.
Unfortunately, I have to completely disagree!
I am not against pie charts in a part-to-whole comparison (see QlikTips: Defending Pie Charts) but using them for this type of comparison is not good.
There are two things being encoded by these pie charts:
- percentage of shares being sold in the IPO - represented in the traditional pie segment fashion.
- volume of shareholding - represented by the size of the circle.
The first is OK in one single pie chart showing one part-to-whole comparison. However, the second is a very poor way for us to compare two values - especially when they are not side by side. Taken together, it is almost impossible to get any real insight from this chart.
As an example, how easy to you find it to compare the 50% pie chart against Tiger Global versus the 6% slice in Mark Zuckerberg's pie? You can't - it just isn't possible. The reality is that the 6% is actually much bigger than the 50% in actual share volume.
I would contend that a bar chart is almost always the best medium to represent this type of information. It is so much easier to interpret and see the values.
Stephen Redmond (@stephencredmond) is CTO of CapricornVentis a QlikView Elite Partner - <h3>Tuesday, 15 May 2012</h3>Good Geographic Charting<br>I am not generally a fan of geographic mapping. I have seen very few examples of it done well.
The problem is often the geography is not always directly proportional to the value being measured. Take this example from The New York Times:
This is not an atypical image that one would see in this area. A color is applied to each of the states to indicate a measure. In this case, it is current Democrat (blue) versus Republican (red) support.By the looks of this map, the republicans are doing very well. But there is a distortion because many sparsely populated states and hence lower electoral college votes (e.g. Montana - 3, Wyoming - 3, Idaho - 4, South Dakota - 3, North Dakota - 3) have a large land mass. On the other hand we have states with small land masses (e.g. New Jersey - 14, Massachusetts - 11, Maryland - 10, Connecticut - 7) that have much higher density populations and hence more electoral college votes. Texas (38 votes) has a much bigger area than other states but it is actually California (55 votes) with the biggest vote. Hawaii (4) is way smaller in area than Alaska (3), but it has one more vote.The New York Times have actually taken a much better approach in their main Electoral Map.
This time they have changed the size of each state to represent the actual number of electoral college votes that they have. Now, the map shows a much smaller swathe of red through the middle and more reflects the reality that (currently) the Democrats have a slight lead.
I really like this because it is a more accurate heat map while still retaining the geographic context.
I thought about how this might apply to Europe. We are used to seeing a map like this (from Google Maps):
But the land area occupied by a country doesn't always reflect its population size. I knocked-up this visualization in QlikView:
I haven't applied any color coding to this yet but it does give you an idea of how the populations are sitting. For example, Sweden has a much bigger land area than Germany (about 450k km² versus about 360k) but has a much lower population (9.3m v 81.7m). Iceland changes from a large island to a small speck in the ocean.
As with all charts, it is important to make sure that context is not skewed. This method of geographic charting helps maintain the contexts.
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner - <h3>Monday, 14 May 2012</h3>Brushing Heatmaps<br>I picked up a fun visualization from Matt Styles on thedailyviz.com called "How common is your birthday"?
Some of the commentary - especially from Andy Kirk - made me think a bit about how we use heatmaps. So, I fired up QlikView, grabbed the data from The New York Times, and opened up a link to Color Brewer.
The map in question shows us the rank of each of the days of the year as regards number of births - rather than the actual numbers. Here is my representation using a QlikView pivot chart:
The code for the color is:
ColorMix1(Rank/366, RGB(0,68,27), White())
I believe that there is a fundamental flaw representing this number of points (obviously 366) in a heat map where the value is the rank versus the actual value. The flaw is that the difference in color does not give us the difference in magnitude between the different blocks.
With this number of blocks, I can see that there is an obvious pattern of darker colors in July-September, but I find it very difficult to pick out, among the sea of darker colors, the ones that represent the actual top ranked days.
Feeling that this might be something that people might want to do, I thought about how I might do it in QlikView and came up with this variant:
Here, I am using a second color range to represent the top 10 (all in September) and the bottom 10 (with February 29th obviously being the lowest).
Essentially, I this is an example of brushing but applied to a heatmap.
The code for the mixed block is:
if(Rank <= 10, ColorMix1(Rank/20, RGB(12,15,124), White()),
if(Rank > 356, ColorMix1((367-Rank)/20, RGB(179,0,0), White()),
ColorMix1(Rank/366, RGB(0,68,27), White())))
It would be a fairly straightforward matter to give the user an interactive facility to turn on/off the Top 10 or Bottom 10.
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner - <h3>Tuesday, 8 May 2012</h3>If you don't know where you are going, then you'll probably get there<br>It is one of the main set of KPIs that a lot of our customers look for in any BI implementation. They want to see performance versus the same period last year. Or perhaps the year to date. Or maybe a moving annual total.
Mr. and Mrs. Doe leave their house to go for a drive to somewhere. After about an hour, Mr. Doe asks Mrs. Doe how far they have come since they left home. She confirms that they have successfully traveled approximately 100km from their point of origin. Satisfied, Mr. Doe continues on.
After about another hour, he asks again how far they have gone. Again, Mrs. Doe confirms that they have traveled approximately 100km from the last checkpoint. They continue on the way. Each time checking that they have traveled the appropriate distance since the last checkpoint. After 4 hours, Mr. Doe is surprised to find that they have arrived back in their home town, but on the wrong side of the tracks, and their gas tank is running low!
Look at the image above. It is a KPI showing that our YTD is well down on the previous year. There must be wailing and gnashing of teeth at the next board meeting. However, let us consider what this is measuring.
Unless you have an extremely stable business, your sales in any one period will actually be influenced by multiple different factors - many of which you have had no control over. It is, effectively, a random number. If I was in the Energy business in the UK, this chart might reflect my business - but is that because of the weather? January 2011 was far milder than January 2010 so my sales will have been down. In 2010, they would have been up and everyone would have been smiling - but it was because of the weather, not anything that I had control over. If I was a retailer, Easter 2010 was in early April so I might have had a seasonal spike in sales in late March. Easter 2011 was late in April so the spike might not have kicked into the QTD figures. Hence sad faces on the shop floor.
What if the figures are up on last year? Does that mean that I need to give everyone a extra holiday to celebrate? Not necessarily. Again, lots of different things might be affecting the numbers. Perhaps you launched a new product. Maybe you hired a whole load of sales people in a new territory and the stock is flying off the shelves. Your business this year will be so different from your business last year that comparing the two is comparing apples and oranges.
There is a better way.
Mr. and Mrs. Roe leave their house in Ellsworth, WI, planning to take a drive to Lake Wisconsin - about 4 hours away. Before they leave, they plan their journey on the map and mark out whey they should be at each hour. After the first hour of the journey, Mr. Roe asks Mrs. Roe how far they are from their first marker. She tells him that they are a little short of it. Mr. Roe is a careful driver and has been driving a bit under the speed limit so he gives it a little more gas.
After about another hour, he checks in again about how for they are from the next target. This time they are a little ahead. He knows that he could take his foot off the gas a little but decides that he wouldn't mind getting there a little earlier. He lets Mrs. Roe know this and she readjusts the markers. They ended up reaching their destination ahead of time and had a great time at the lake.
A business should not be relying on random events to compare how they are doing. If I am a retailer, I will know when the major holidays are going to occur. I will look at my last year figures, apply some thought as to where I can see growth, apply some mathematics, and come up with a sales forecast for the year - a forecast that should reflect the strategic direction of the company. If things change (like a really good summer!), I can change the forecast to reflect things. If I am in the energy business, I will be constantly looking at long range weather forecasts and modifying the sales forecast.
So, unless your year-on-year business is extremely stable, comparing one set of effectively random numbers against another set isn't really going to tell you much about your business. Comparing them against a well thought out and planned set of numbers is going to tell you exactly where you are going and then the KPI is going to let you know if you need to intervene and change things - exactly what a KPI should do.
If you don't know where you are going, then you'll probably get there.
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner - <h3>Monday, 23 April 2012</h3>Change Their World<br>A guy I never met before just gave me $10 cash. He challenged me to turn that $10 into $100. His name is Phil Wall and he runs Hope HIV.
As it happens, I recently started running and am training for my first ever road race. It is only 4k but we all have to start somewhere. The race is in 2 weeks and training is going well. I thought that it might be a great idea to combine the two and make that $100 from sponsorship.
The choice of HopeHIV was prompted by QlikView's "Change Their World" program. The company deserves a lot of respect for their sense of social responsibility.
HopeHIV is a great cause and I hope you can help me out.
You can contribute at my JustGiving page - the money goes straight to the charity.
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner - <h3>Friday, 30 March 2012</h3>Miami here I come<br>It's that time of year again where I am looking forward again to "Qonnections", the QlikView global partners conference. This will be the 3rd year in a row where we will be at the Fontainebleau Resort in South Beach, Miami Beach. It kicks off on Sunday 22nd April and finishes up on the following Wednesday.
I have to say that my favourite Qonnections was 4 years ago in the Lowes Hotel, also in South Beach. There were a number of reasons:
- Jonas Nachmanson, then CTO, announced Set Analysis coming in version 8.5. The techies in the audience gave the announcement a standing ovation.
- Stephen Few delivered the keynote speech on the first morning. This changed pretty much everything about the way I have delivered QlikView projects since then.
- Capricorn Ventis won the award for the fastest growing partner. It was a great experience to win an award at an event like this.
- I attained certification in the brand new "Professional" Certification program. Earlier that year I had been one of the first people in the world to achieve the new "Developer" Certification.
So, there were a lot of highs that year. I would have to say that subsequent Qonnections were somewhat lesser in the number of highs.
I think that maybe, for me, it is that Set Analysis was the last "great" innovation in QlikView. There have been a lot of "nice" features in subsequent releases, and good features to make the product more enterprise ready, but none of them changed the way that I used the product so fundamentally. I don't think that there has been 1 .qvw that I have created in the last number of years that has not used Set Analysis.
I have an idea that this year might have some more interest. I think that v12, whose features (without promises of final delivery) will probably be announced to partners next month. I don't know, but I suspect that there will be some really interesting things happening and perhaps some fundamental changes. Of course, I will be restricted from commenting on them until they are made public, but I might whet your appetite.
Enjoy the tune - Will Smith, Miami
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner - <h3>Wednesday, 21 March 2012</h3>Where are the Irish going? Another poor pie chart.<br>Oh dear. Yet another poor example of a pie (in fact a doughnut) chart to try and get across something that is very important to Irish people.
David McWilliams is a leading Irish economist. One of the few to predict trouble many years before the crash, mostly not listened to. I think that he has a wonderful grasp of world economics and how they apply and interlink with Irish economics. Unfortunately, when he described the chart below as a "wonderful graphic on Irish emigration", he showed that, like many, he doesn't know much about the presentation of quantative data.
The chart in question is found here: http://www.locusinsight.com/Ireland_files/Emigration/chart.html
It is an "interactive" doughnut chart where each segment represents the number of people going to different locations around the world.
Besides all the usual issues with pie type charts (my blog post on "Defending Pie Charts" might be a good place to start reading about the problem with pies; and of course, Stephen Few's Save the Pies for Dessert), the whole interactive thing makes it even more difficult to grasp the numbers and the relationship between them.
I grabbed the numbers from the chart into QlikView and created a much simpler, but far more effective, line chart:
(click for larger image)
From this chart, it is quite easy now to see that there is a general trend upwards in emigration and the UK and Australia are the main destinations. There is a huge spike in EU12 (the 12 countries that have joined the EU since 2004) during 2009.
I had my suspicions of what that was so I went to the Irish Central Statistics Office website and retrieved the figures for emigration by nationality.
(click for larger image)
This confirmed what my suspicions were - many of the eastern Europeans (Polish, Lithuanian, Latvian, etc.) who had emigrated into Ireland during the Celtic Tiger years, many of whom worked in the construction industry, left in 2009 because of the collapse of that industry.
"Fancy" charts often actually hide the numbers that people want to see. Here we have two very plain and simple line charts that tell much more of a story.
It is not a great story if you are Irish.
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner - <h3>Sunday, 18 March 2012</h3>What are the colour of your eyes?<br>I often find myself reading other blogs, in the name of "research", and finding myself jumping from one link to another, following one idea to another, storing lots of fascinating facts within my grey matter (in theory).
One of my favourites is Robert Kosara's Eager Eyes blog. Recently, he was discussing writing as a metaphor for visualisation which led me, via a couple of hops, to an IBM research piece called "How NOT to Lie with Visualization", written by Bernice E. Rogowitz and Lloyd A. Treinish of the IBM Thomas J. Watson Research Center, Yorktown Heights, NY.
The article is a discussion of colour maps used in visualisation. It is worth reading.
My own rule of thumb for a colour map is to use just the one hue - e.g. Blue, because it is relatively neutral - and change the lightness of the colour from one end of the scale to the other (e.g. using ColorMix functions in QlikView). If I need diverging scales, then use two different hues that go from light in the centre to dark at the extremities. For this, I always imagine an old ordinance survey map that shows sea and land - as the sea gets deeper, the blue gets darker; as the land gets higher, the brown gets darker.
Of course, there is always the question of ability to discern the difference in colours in between those extremities and whether a colour map is suitable at all - some humans are much better than others. So what can we do for those of us whose eyes are not so good?
In many cases, I will implement a simple rule - anything that needs investigation based on the business rule gets coloured, everything else stays neutral. - <h3>Monday, 13 February 2012</h3>Move your QlikTech ProgramData folder<br>There are a number of reasons why you would want to move the C:\ProgramData\QlikTech folder to somewhere else. For example:
- Disk size issues
The C: drive too small and the QlikView performance logs, reload logs, etc. are filling it up.
- Failover
You have a "cold" standby server and it would be much better to have the ProgramData\QlikTech folder on the SAN drive so that when the cold server boots up, it will already have all the settings, schedules, etc. from the old server.
There are ways of moving the ProgramData folder - the supported one from Microsoft (with caveats!) is to do it during the installation of Windows - http://support.microsoft.com/kb/949977
This isn't really going to be an option for you if the server is already built by the nice friendly folks in the IS department. They might also object to you trying to move the whole ProgramData folder anywhere else, just for QlikView.
I have run into this situation a couple of times now and there is a really neat solution - move the QlikTech folder to its new home and then create a symbolic link in the old ProgramData folder. Everything will still work perfectly!
To do this:
1. Stop all of the QlikView services.
2. Move the QlikTech folder from C:\ProgramData to its new location, for example E:\ProgramData.
3. Run a Command Prompt in Admin mode. CD to the C:\ProgramData folder and run the following command:
MKLINK /D QlikTech E:\ProgramData\QlikTech
4. Restart all of the QlikView services.
All done. Do the same on the "Cold" server (you will need to bring down the "hot" server first) and create the same symbolic link to the same SAN location and it will work as if it was the "hot" server and no need to ship settings files.
Hopefully you will find this useful.
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner - <h3>Monday, 13 February 2012</h3>Printing Reports to PDF using PDFCreator<br>PDF Creator is a nice tool for generating PDFs and it comes at a very attractive price. Like other tools of this ilk, it creates a Printer driver which you can print your documents to. Unlike other drivers (especially at this price point), it gives you additional control on what is printed using a COM interface. This makes is quite handy to use from VBScript - either inside or outside QlikView.
I am going to give you some starter code here on how to do this from within a QlikView document using a Macro. You can also do this from a .vbs file that is external to QlikView. Just note that if you are using a .vbs then you will still need an appropriately licensed copy of QlikView Desktop to run this.
*** All script here is completely unsupported by myself or anyone at QlikTech. Use at your own risk ***
If you are going to use a .vbs then you will need to create your own ActiveDocument variable. This is how I do that:
Dim QV, ActiveDocument
set Qv = CreateObject("QlikTech.QlikView")
QV.OpenDoc Document,"",""
set ActiveDocument = Qv.ActiveDocument
In this case, the variable Document contains the full path to my .qvw. The 2nd and 3rd parameter are a QVUser and Password - if you have those in Section Access. Once you have an ActiveDocument object, the code is the same between the Macro and the .vbs.
Usually when I do this, I am going to loop across all the value in a field. For example, I may have a field called "Seg" and I want to run a report for each value in this field.
I may choose to clear all values first:ActiveDocument.ClearAll
Either way, I can call the GetPossibleValues to get a list of all the values in that field:
Dim FieldName
FieldName = "Seg"
set mySelections = ActiveDocument.Fields(FieldName).GetPossibleValues
Now, I can loop through the values, select each one in the document and call a function that I have made called Print_PDF for each value:
Dim i
for i = 0 to mySelections.Count - 1
Dim FieldValue
FieldValue = mySelections.Item(i).text
ActiveDocument.Fields(FieldName).Select FieldValue
Print_PDF FieldValue, "My Report", "RP01"
Next
Print_PDF is, essentially, using the reference code from the PDF Creator documentation:
Sub Print_PDF(FieldValue, ReportName, ReportID)
' Designed for early bind, set reference to PDFCreator
Dim pdfjob
Dim sPDFName
Dim sPDFPath
'/// Change the output file name here! ///
sPDFName = ReportName & " - " & FieldValue
sPDFPath = "C:\PDFReports"
Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")
With pdfjob
If .cStart("/NoProcessingAtStartup") = False Then
If .cStart("/NoProcessingAtStartup", True) = False ThenExit Sub
End if
.cVisible = True
End If
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With' Print the QlikView ReportActiveDocument.PrintReport ReportID, "PDFCreator"
'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
ActiveDocument.GetApplication.Sleep 20
' in VBScript use WScript.Sleep(20)
Loop
pdfjob.cPrinterStop = False
'Wait until PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
ActiveDocument.GetApplication.Sleep 20
' in VBScript use WScript.Sleep(20)
Loop
pdfjob.cClose
Set pdfjob = Nothing
End Sub
The only additional piece here from the PDF Creator documentation is the QlikView call to Print the report:
ActiveDocument.PrintReport ReportID, "PDFCreator"
I pass the field value and a ReportName value into the function so that I can generate a different PDF file name for each field value.
Last thing is that you will need to enable System Access in the Macro settings. In a way, this is where .vbs has an advantage. Because the script is already external, there is no issue with enabling System Access.
Enjoy. Just remember, this is all unsupported so use at your own risk.
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner
