Tuesday 23 April 2013

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 1Option 2Option 3
InitializeSelectInitializeSelectInitializeSelect
219125219125219125
250140250125202125
234125265156203124
218125219125218125
250125218141218140
219125281156250156
234141218141219140
219125218125297141
Mean230.4128.9236.0136.8228.3134.5
Mode219.0125.0218.0125.0219.0125.0
Median226.5125.0219.0133.0218.5132.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

Wednesday 17 April 2013

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