Performance using Labels or Column() in charts

If I have a chart that calculates Total Sales: Sum(Sales), and Total Costs: Sum(CostPrice*Quantity), then I have 3 options to calculate the Total Margin:

Option 1:  I can just use the same expressions in full:

Sum(Sales) - Sum(CostPrice*Quantity)

Option 2:  I can reference the column values using the Column() function:

Column(1)-Column(2)

Option 3: I can reference the labels of the first expressions:

[Total Sales] - [Total Costs]

Now, someone once said to me that the last option, although it might appear to be the best because you are re-using existing values, actually calculates slower than the others.  I have often wondered if that was true.  Someone else suggested to me recently that the 2nd option actually performs quicker.  I decided that I should find out!

Buried deep in the Document Properties of a QVW, on the Sheets tab, there is a list of all the objects on the sheet along with their last calculation time (CalcTime) in milliseconds.  If you looked at this for one of your charts, you might find that it says "0".  This is because the last calculation might have been from cached data so the only accurate CalcTime is when the chart is first initialized in QlikView or when the first of a particular set of selections are made in a recently opened document - where the cache has not been established yet.

So, what I did was to take a document with about 5,000,000 sales transaction rows and build a simple Straight Table of Sales / Costs / Margin by Country.  I made 3 copies of this document, each copy making the Margin calculation in one of the above ways.  The only object in each document is the straight table.

I then completely closed QlikView.  I opened one of the documents, recorded the "Initialize" CalcTime for the chart, made a selection of 2 countries (UK and USA) and recorded the "Selection" CalcTime for the chart.  Then I shut down QlikView again and repeated.  I did this 8 times for each of the documents, opening them in random order.

Here are the results:

	Option 1		Option 2		Option 3
	Initialize	Select	Initialize	Select	Initialize	Select
	219	125	219	125	219	125
	250	140	250	125	202	125
	234	125	265	156	203	124
	218	125	219	125	218	125
	250	125	218	141	218	140
	219	125	281	156	250	156
	234	141	218	141	219	140
	219	125	218	125	297	141
Mean	230.4	128.9	236.0	136.8	228.3	134.5
Mode	219.0	125.0	218.0	125.0	219.0	125.0
Median	226.5	125.0	219.0	133.0	218.5	132.5

There is some slight differences.  But, statistically, I would have to say that there is no real difference between the 3 options!

One thing that I would say is that I don't like using the Column() function in a Straight Table.  This is because the user may have the option to swap columns around and that would invalidate the expression.  Other than that, it appears that you can just use whatever option you like and it won't make any difference to QlikView performance.

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Old home, new address - Welcome to qliktips.com

I have been thinking about this for a while and decided that there was no reason not to "brand" my blog a little.  So, welcome to qliktips.com.

The site is still hosted by BlogSpot.com (Google) and all the old content is still available and all the old links are still working.

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Coming soon, to a bookstore near you...

Today is a significant milestone for me.

Back in January, I was approached by Packt Publishing to author a book for them.  They wanted to follow up on the success of Barry and Mike's QlikView 11 for Developers with an addition to their Cookbook series.

A Cookbook is something very different than Barry & Mike's excellent book.  Their book is an excellent instructional manual for teaching beginners, and even more experienced developers, how to create QlikView applications.  They start at the basics and move through the book to the more advanced subjects.

Like an actual cookery book, I will assume that the user knows how to do certain things, like how to create a new QlikView document and edit the script.  I will say, "edit the script", in the same way that the cookery book will say, "preheat the oven to 350F", and I will assume that you will know how to do that.

The Cookbook will be a collection of self-contained recipes collected in broad categories.  You don't need to read the book from start to finish, a user can dive into any recipe without having to have completed any before (mostly!)

Right now, the categories are:

- Charts

- Layout

- Set Analysis

- Advanced Aggregation

- Advanced Coding

- Data Modelling

- Extensions

- Useful Functions

- Script

- Improving Performance

- Security

Some categories only have a couple of recipes, some have many.  Right now, there is a total of 79 recipes.  This could, of course, change before publication.

Today is significant because I have just delivered the final first draft to the publishers.  Now, this doesn't mean that the book will be in the stores next week, but it is a significant step along the way.

The next few months will, I am sure, be interesting, but I can't wait.

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

What is a "Data Scientist" and how do I become one?

I read an interesting post yesterday by Daniel Tunkelang of LinkedIn called "Data Science: What's in a name".

Essentially, a Data Scientist is someone who applies the scientific method - explore, hypothesize, test, repeat - to data.  This is probably what a lot of "QlikViewers" and other "data discovery" (Tableau, Spotfire, etc.) experts think they do.  But there are potential gaps that need to be considered.

Daniel introduces the Data Science Venn Diagram from Drew Conway in his article:

My experience is that a lot of data discovery experts come from an IT background - databases, reporting, etc.  Very much a "computer science" type of person.  They may have built up a lot of the substantive expertise in many areas of business and probably have a great set of "hacker" skills that they have built up over the years, but perhaps a lack of the core statistical skills or understandings.

This is where I found myself over the last number of years.  I have a great range of "hacking" skills and abilities to be able to get at data and get it into a form that I can use it to answer the business questions where I can apply my built-up business expertise.  But Drew Conway identifies this as "Danger Zone!" on his venn diagram - someone who knows enough to be dangerous.

I think that I recognized this in myself a while ago, so decided to take action.  Starting with just reading - I highly recommend How to Lie with Statistics by Darrell Huff - and then moving on to taking Statistics One on Coursera.  I even find that regularly listening to More Or Less from BBC Radio 4 is an education in itself.

Massive Open Online Courses (MOOCs) are a great way for people to educate themselves about the gaps in their knowledge.  They do have a commitment in time, but I think that it is worth it.  One of the great things about it is the ability to interact with others from right around the world.

I see a new one from Coursera called Introduction to Data Science is scheduled to start in April 2013.  Time to sign yourself up?

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

QlikView 11 for Developers, A Review

It seems like a long time ago, but all the way back in June of 2012 I was delighted to receive a request from Barry Harmsen to become a technical reviewer on a book that himself and Mike Garcia were writing about QlikView.  I was quick to accept.

At the time, there were a couple of eBooks on QlikView available but there was also an "official" book in the offing (that, to date, doesn't appear to have been realized) but Barry was quick to say that his and Mike's book would compete on depth and quality.

To be honest, I feel that the review process was quite easy on me.  This is because Barry was living up to his promise and the quality of what was coming out in draft was very good.  I did make some suggestions as to changes or rearrangements, but very few - the red biro still has plenty of ink left.

I didn't get to review every single chapter, but I could tell from what I was seeing that Barry was living up to his other promise and the depth of the book is also excellent.  When my copy of the published book arrived just before Christmas, I was able to see the whole picture and confirm the depth.

This book has everything that a new QlikView developer will need to get started.  But not just that, it also has a lot of tips in there for the experienced developer (I know because I found some myself!)  Even if you have a few years experience in developing QlikView, you will find nuggets of useful information in here.

I love the way the book is structured for the beginner.  Taking you from a great introduction of how QlikView works, through building some simple interfaces, diving into the QlikView script, advanced subjects like Set Analysis, and then all the way through to QlikView security - a non-trivial subject.

No book can teach a new developer everything.  You still need to cut your teeth on actual projects to hone your craft.  But this book will get you a good way down the road to success.

Highly recommended.

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Olympic Medal Table - Another Redmond Profile chart

When I originally posted the Redmond Aged Debt Profile Chart, Dmitry Gudkov pointed out that it was much better than a stacked bar chart.  Of course, he is correct, it is.  So when I saw something similar to this yesterday (my data from http://www.london2012.com/medals/medal-count/):

I knew that I had to knock up a quick profile chart to replace it.  It didn't take long (it doesn't):

Visual, interactive, easy to use and interpret.  The other thing I like about this approach using QlikView's Straight Table is that it is easy to add the numbers in if necessary - although I feel that the numbers detract from the visual experience here.

What else can you do with it?

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Powerful Preceding

The preceding load functionality in the QlikView load script is extremely powerful for transforming data.

Many developers will never use it.  They simply use SQL queries to format their data and transform it.  Others may use it - but accidentally.  They turned the "Preceding Load" check box on in the "Select" data wizard during training and have never turned it off.  They get a simple SQL statement like:

SQL SELECT * FROM TableName;

And barely notice that there is a QlikView Load statement sitting above it.

This is brilliant though - just because it is a QlikView statement.  This means that we can use the great set of QlikView functions within this statement.  From the simple Year or Month, to parse a date, to the most useful ApplyMap - one I use all the time.

Even there, we are not finished.  We can add additional Preceding Load statements above other Preceding Load statements!

Another thing that some people may not realize is that Preceding Load statements can include their own WHERE and GROUP BY clauses.  This is really powerful.

For a quick example, I want to load in a list of Father Ted episode from:

http://en.wikipedia.org/wiki/List_of_Father_Ted_episodes

I start off with the normal QlikView file wizard:

I see an immediate problem here.  There are 3 columns but there is a 4th column of data wrapped onto the next row.  The simple QlikView script to load this will look like this:


LOAD F1 as Episode, 
     Title, 
     [Original airdate]
FROM
[http://en.wikipedia.org/wiki/List_of_Father_Ted_episodes]
(html, codepage is 1252, embedded labels, table is @3);


Looking at this in a table box shows me the problem.

I need to come up with a simple rule - if the Episode field is numeric, that line has Episode, Title and Original airdate.  If not, that line has the description.  I can then re-craft my load like this:


Load
if(IsNum(Episode), Episode, Previous(Episode)) As Episode,
if(IsNum(Episode), Title, Previous(Title)) As Title,
if(IsNum(Episode), [Original airdate], Previous([Original airdate])) As [Original airdate],
if(IsNum(Episode), Null(), Episode) As Description;
LOAD F1 as Episode, 
     Title, 
     [Original airdate]
FROM
[http://en.wikipedia.org/wiki/List_of_Father_Ted_episodes]
(html, codepage is 1252, embedded labels, table is @3);


Here I have put a Preceding Load on top of the original.  I am checking if the Episode value is numeric.  If it is, I use that value for Episode and use Title for Title and Original airdate for Original airdate.  I use the Null() function to create a blank Description field.  If the Episode field is not numeric, I then grab the Previous values for Episode, Title and Original airdate and use the new text value of Episode as my description.  This leaves me with:

Now, I can see that I have the first 3 values populated on each row, but Description only on every 2nd row. I am going to make one last tweak to my code:


Load
if(IsNum(Episode), Episode, Previous(Episode)) As Episode,
if(IsNum(Episode), Title, Previous(Title)) As Title,
if(IsNum(Episode), [Original airdate], Previous([Original airdate])) As [Original airdate],
if(IsNum(Episode), Null(), Episode) As Description
Where Not IsNum(Episode);
LOAD F1 as Episode, 
     Title, 
     [Original airdate]
FROM
[http://en.wikipedia.org/wiki/List_of_Father_Ted_episodes]
(html, codepage is 1252, embedded labels, table is @3);


By adding a Where clause to the Preceding Load, I am now excluding the lines that do not have the Description.  My mind slightly boggles at this because I am not sure how I would achieve this quickly and easily using a SQL Query.

Can you think of any ways where a Preceding Load might save you multiple steps elsewhere?

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond