Friday, 21 March 2014

Generate and execute a batch file in QlikView

My good friend and fellow Irishman, Alan Farrell, pinged me on Twitter this evening with this question: "can you create a .bat file in qlikview and save the file to a specific folder"

There is a pretty easy way of generating a file in QlikView and that is to load the rows of text that you want in that file into a table and then use the Store command to write that table to a text file.  If there is only one column in the table, then no separators will be written.

One "gotcha" about writing text files like this is that the field name will always be written.  This is easy to get around in the case of a batch file because you can name the field to some valid batch syntax - like "@echo off" or "REM Start of Batch File" - and that will be fine.

Here is an example to generate a batch file that will move all of the text files from one location to another (pretty lame example!):

'REM This is a batch file test' As [@echo off]

For Each vFile in FileList('c:\temp\Folder1\*.txt')

'MOVE $(vFile) C:\Temp\Folder2' As [@echo off]


Store BatFile into c:\temp\test.bat (txt);

Drop Table BatFile;

EXECUTE c:\temp\test.bat;

Now, the final execute will only work in QlikView if you have the "Can Execute External Programs" turned on in the Settings tab inside the Script Editor.  If you have publisher, you can have the QlikView task generate the .bat file and then have Publisher execute it.

Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He 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

Sunday, 16 March 2014

Same day last year

Many companies - especially retailers - deal with comparisons of week, this week versus last week, etc.  Very often they will want to compare the performance of one day in a week versus the same day last year - the "Like-for-like" day.

I have seen some developers tie themselves up in knots trying to calculate things based on different rules and taking leap years into consideration.  But it is often much simpler than they realise!  No matter what year, leap year or otherwise, the same day of the week last year is exactly 364 days ago.

   364 = 52 x 7.

One thing that I can't guarantee with this calculation is that the same day last year will be in the same week number.  This is because of the fact that some years have 53 weeks and some have 52.  The ISO rules define week 1 as the week on which the first Thursday of the year falls.  Different organisations may have different rules about the week numbers, such as week 1 always starts on the 1st January, or even April 1st.  If you need to compare weeks based on week number, rather than the 364 rule (so how do you compare week 53?), then the best way to handle that is to have a calendar table that defines the values.

Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He 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, 26 February 2014

UX, Data Visualization, and the case for cognitive dissonance

In his seminal work on user experience (UX) design, The Inmates Are Running The Asylum, Alan Cooper introduces the term "Cognitive Dissonance", which in psychology means the stress caused by holding two different ideas at the same time.  Cooper uses this in the context of a computer system performing an action in a way that is different from the idea that a user might expect it to.  Cooper, correctly, argues that computer systems should be designed better to avoid such cognitive dissonance.

But perhaps there is room for some cognitive dissonance in data visualization?

A couple of years ago, I heard a senior person in a particular BI vendor discuss the work of Dr. Daniel Kahneman.  Dr. Kahneman has written an excellent book called Thinking Fast and Slow.  In this book, we are introduced to the idea of System 1 and System 2 thinking.  System 1 thinking is fast - largely subconscious thinking.  Imagine answering the question, "what is 2 + 2?"  The answer comes immediately.  System 2 thinking is more conscious, deliberate, and often logical.  Think of answering the question, "what is 17 x 22?"  We need to follow a process to come up with the answer and it is a lot harder.  Because it is harder, and uses more energy, our brain much prefers to use System 1 thinking.

The BI vendor in question was starting along a path where they were suggesting that it would be good for visualization designs to support the System 1 thinking - make things more automatic and easier for users.  If they don't have to think to hard about decisions, then we make their lives easier, right?  Having read Dr. Kahneman's book, I am glad that they didn't pursue this messaging in their global marketing, because it might just be very wrong.

Last Monday 24th February, the BBC's Horizon series covered the subject, How You Really Make Decisions.  I heartily recommend it if you can catch it on the iPlayer or if it is repeated on a local station (Horizon has always been one of my favourite programmes on TV).  The program looked at Dr. Kahneman's work.  It appears that System 1 thinking is actually more associated with making mistakes!

You may have seen this video before:

This is a great example of inattentional blindness.  It was created by researchers Chris Chabris (Union College, NY) and Daniel Simons (U. of Illinois).  This is following the case of Boston police officer, Kenneth Connolly who was pursuing a murder suspect on foot when he passed some other police officers beating up another suspect.  Connolly completely missed the beating up.  However, he was convicted of perjury because the jury couldn't believe that there was any way that he couldn't have seen it.  However, Chabris & Simons do regular tests where they have people running after a jogger while having some guys having a fight along the route - 50% of the people do not see the fight!

So, how does this apply to UX and Data Visualization?  Well, I think that Cooper's UX rule on cognitive dissonance might not hold all the time in the case of a dashboard or analysis piece.

We know that a dashboard that is over complex with too many pieces of information is difficult to use and makes a user work very hard to find the information that they are looking for.  There is a danger that they might miss a tree because of the forest of options.  However, the idea that we should design a dashboard that a person can just look at and see the answer immediately may not be right either.

The problem is that an easy to use dashboard may pander to our System 1 thinking.  But System 1 thinking can also be influenced by a whole load of cognitive biases that we as designers may not be aware of - some of which we could even be accidentally programming by our design.  We might think that a dashboard is simple and that the answer is obvious to everyone - but that is not always the result.

So, perhaps there is a case for a deliberately conceived cognitive dissonance being built into the dashboard?  Force the user to think twice.  Make them engage their System 2 thinking to come up with the answer.  Don't make it easy, make it deliberately harder.  Perhaps that might actually help users make better decisions.

Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO and Qlik Luminary at CapricornVentis a QlikView Elite Partner.
Follow me on Twitter: @stephencredmond

Thursday, 20 February 2014

#QlikTips Tweet Chat

Just because I am now a QlikView Luminary doesn't mean that I plan to just sit on my laurels!  So, seeking new ways to share tips and tricks from the QlikView world, I have decided to start off a Tweet Chat to see if making the process more interactive will be helpful.

There are a few rules that need to be established before kicking off:  #QlikTips Rules.

I'm going to use Twubs to manage the whole thing:, but there is no restriction on anyone using it.

First one will be on Friday 21st February at 16:00 GMT.  According to the World Clock, that is 11:00 in New York and 21:30 in Mumbai.  I hope that turns out to be a suitable time for folks.

See you there!

Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO and QlikView Luminary at CapricornVentis, a QlikView Elite Partner.
Follow me on Twitter: @stephencredmond

Monday, 10 February 2014

What is "self-service BI"?

I don't always get to sit in on the #BIWisdom TweetChat, hosted by Howard Dresner of Dresner Advisory Services - the time zone difference means that it is usually during my Friday commute - however, I was glad that I caught this weeks because it allowed me to articulate and explore my own ideas about what self-service BI is.  I used some analogies to help make my argument.

My first analogy was to imagine the delivery of BI as a self-service restaurant.  I said that self-service BI was the "user's ability to select what they want from the menu and eat it".

In this scenario, the "chef" will prepare the meals as he sees fit and present the finished plate for consumption.  The "diners" are able to select some different options and different combinations but they are beholden to the chef to make those options available.  The chef knows all the ingredients and has a good idea of what the diners will want to eat.  If the diners are bored with the menu, they can ask the chef for new recipes, but he will make the decision as to whether they can be delivered.

As the restaurant gets busier, more chefs will be employed.  It is important to then employ a restaurant manager to make sure that all the chefs are using the right ingredients and delivering the right dishes to the diners - nobody wants the get a sick stomach!

I think that this is the model of self-service that organizations will implement today.  It is not necessarily a bad model for them to use.  Centralize the data preparation and control what is delivered to the business.

The second analogy is of a grocery store.  In the store there is also a good range of "ready meals".  Someone has prepared these based on what they know shoppers will want to buy and many shoppers will go straight to this aisle and ignore the other produce - and why not, it gives them exactly what they need.

As well as the ready meals, there is a whole range of other food available, from partially processed down to raw.  More adventurous shoppers can pick what they want and then combine it however they choose, so as to create their own meals.  They take responsibility for whatever ends up on their plate.

In this scenario, the most important role is the store manager.  While he may have instructions from "head office" in the range of produce that will be offered, he is responsible to make sure that everything is fresh and edible - everything must be "FDA approved"!

Yep, it's good, old-fashioned, governance.  In either scenario, the important role is the one that prevents the diners from getting food poisoning.

Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He 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

Thursday, 6 February 2014

The Spanish Inquisition

Nobody expects the Spanish Inquisition!

Monty Python - The Spanish Inquisition

I was showing some QlikView "virgins" around the product yesterday and came to the subject of mapping tables - one of my most frequently used functions in QlikView script.  I came to reciting the two rules that I always give for mapping tables:

1.  There can be only 2 fields, and
2.  The names of the fields are not important, it is the order of the fields that matters.

And then I found myself reciting the 3rd of the two rules:

3.  An almost fanatical devotion to the Pope!

Needless to say, with the average age of the people that I was presenting to being somewhat lower than my own, none of them had a clue as to what I was going on about!  I got a few strange looks.

It appears that while treading a well worn path - I have recited these rules many times - my brain decided to head off on a different path and I found myself in the shoes of Cardinal Ximénez while reciting the number of weapons available to the Spanish Inquisition.

Perhaps I could describe it as a "QlikView moment"?  Just like when working with QlikView, my brain put two things together that hadn't previously been thought about.

Isn't this the most wonderful thing about QlikView?  The whole discovery side of things?  But how many of us create QlikView applications that do not let the user discover?  How many have a set path that gives the answers that were asked for and no others?  I know that I am guilty of a few.

In his recent blog post, The Key to Heaven, Henric Cronström gives us some excellent advice on how to enable the user.  We should trust our users more and let them discover - who knows what gems will be revealed.

By the way, the Spanish Inquisition was expected by everyone that they visited!  Apparently they gave people 30 days notice:

QI - Spanish Inquisition

Stephen Fry, the font of all knowledge.

Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He 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

Monday, 23 December 2013

Key Performance Indicator Approaches (Redmond Pie-Gauge)

The "traditional" approach to presenting Key Performance Indicator (KPI) values on a dashboard has been to use circular gauge.  Something like this:

Stephen Few has proposed the Bullet Graph as a much more effective visualization, and I tend to agree with him, but there is just something about circular objects that appeals to people.  Even though we tell them that Pie Charts don't work as well as Bar Charts for a certain visualization, they users demand Pie Charts.  Propose a Bullet Chart and they request a gauge.  I tend to push back on these requests, but not always militantly.  I will allow them because I understand that there is an emotional element within dashboard design that needs to be catered for.

When I do push back though, I like to be able to present alternatives.  Within QlikView, we have several different options for presenting KPI type information.  For example, the data can be presented in a straight table with a linear gauge:

The gauge here is effectively acting as a modified Bullet Graph.  I discuss the creation of this in my QlikView for Developers Cookbook.

Another effective way of presenting this information is by using a horizontal bar chart to display the percentages:

This chart quickly shows the user the relative performance of each country.  The colored blocks show the extent by which a country has exceeded, or failed to reach, the target.

Building a chart like this led me to think about other ways that this could be presented and led to the design of the Redmond "Pie-Gauge" (*** can't find anything like this usage online, but let me know if you have seen it before ***):

This, I think has a couple of things going for it.  Firstly, it works like a good pie chart should - low cardinality part-to-whole comparison.  The circle represents either the total sales or the total target - whichever is higher.  The segments add up to the whole.  It also works as a circular gauge, showing the extent of good performance to the right and sub-target performance to the left.  The size of the segment shows how that plus or minus performance compares to the whole.

So, it gives users the circular objects that they have an emotional attachment to and also gives a reasonably effective visualization.

Personally, I like my new chart design, but it might not be my first recommendation as a gauge alternative.  I quite like this Bar Chart representation:

Here, we can see the percentages as sized blocks versus the actual values.  We can quickly see that although Germany exceeded target by 25%, the Japan 20% represented a much larger actual value.  Similarly, the US 5% drop versus target is actually a much larger dollar value than the UK's 7.1% drop.

I would be interested in hearing other peoples' points-of-view on this subject and what they think about the Pie-Gauge.  Would it be useful for me to follow up with a post on how to create it?

Stephen Redmond is author of QlikView for Developer's Cookbook
He 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