Sway to the Qlik

Microsoft have made their new Sway service generally available on "Preview".

So, what is this Sway?  Really, it is about telling stories - without having to know anything about design or building websites, you can upload some images, type in some text, and publish your story.  Your story is then viewable by any user, on device, in a responsive manner.

For example, witness the rather amusing antics of Flat Tony.

You can have your story go horizontally, or you can have it go vertically.  You can easily arrange things if you don't like the way the system presents them.  More ways of arranging things will be coming on stream quite soon.

Why might this be of interest to a Data Viz professional?  Because stories are what we tell - stories about data.

One of the features of Qlik Sense that most interested me was Stories.  One of the features of Qlik Sense that most disappointed me was ... Stories.  The concept is brilliant - easily snapshot images of my charts and then embed them in a story along with annotations to explain what is happening.  The problem is that I can only share these with other users of Qlik Sense - I can't export them to any other format or push them out on a website to non Qlik Sense users.

There is a logic to these restrictions.  The features of being able to view the charts "live" from within a story would not be available to external users.  OK, that can be an important thing, but is that the most important thing?

Here is something that I built in about 10 minutes using Sway (it may look familiar!):

Food Corp Sales Analysis 2014

It isn't earth shattering, but it wasn't several days of effort either.  I used the Windows Snipping tool to grab the images (apparently I can use APIs to get snapshots too), and just added the text.

The big deal about this link is that you can view it - and you can view it on any device.  And you don't need to have a Qlik Sense license.

I am sure that we will see Qlik Sense Stories evolving over time - this is definitely a direction that I would like to see them going.

---

Stephen Redmond is author of Mastering QlikView, QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a Qlik Elite Partner.
 

Season's Greetings

This QlikView script may be the most important one that you ever see (or not, mostly not).

Follow these steps.

1.   Create a new QlikView application and add the following script:

   For i = 0 to 100 step 5

Tree:
LOAD
RowNo() as id,
1 as Period,
$(i) As Branch,
-100+$(i) as X,
$(i)+10 as Y
AUTOGENERATE (1);

LOAD
RowNo() as id,
2 as Period,
$(i) As Branch,
0 As X,
$(i) As Y
AUTOGENERATE (1);

LOAD
RowNo() as id,
1 as Period,
$(i)+1 As Branch,
0 As X,
$(i) As Y
AUTOGENERATE (1);

LOAD
RowNo() as id,
2 as Period,
$(i)+1 As Branch,
100-$(i) as X,
$(i)+10 as Y
AUTOGENERATE (1);

   Next

      Reload the document.

2.   Add a new Scatter Chart.

3.   On the General tab, set the Title in Chart to:

   =chr(8902)

      In the Title Settings, set the font size to 26 and turn on Bold.
   
4.   On the Dimensions tab, add Period and Branch.

5.   Turn on Advanced Mode on the Expressions tab.  Remove the expressions that have been added and add the following 3 expressions:

   =X

   =Y

   =10+Avg(fabs(X))

6.   Under the first expression, set the following Background color expression:

   =RGB(1, 121, 111)

7.   On the Style tab, select the flat, connected bubbles Look (3rd one down in the 2nd column).

8.   On the Presentation tab, turn off Show Legend.  Add a Reference Line on the X-Axis with a value of 0.  The color should be the same RBG as in step 6.

9.   On the Axes tab, turn off the Forced 0 option and turn on the Hide Axis option for both axes.  For the Y Axis, set a Static Min of -5 and a Static Max of 110.

10.  On the Layout tab, turn off the border (or set to 0 pt).

11.  On the Caption tab, set an appropriate caption.

With a little tweaking (using Ctrl+Shift to move bits around), you should be able to come up with something like this:

---

Stephen Redmond is author of Mastering QlikView, QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a Qlik Elite Partner.
 

Searching Questions

I had the privilege recently of visiting Qlik Labs (or "Q Branch", as I like to call it) in London and listened to Alistair Eaves, the Director of Qlik Labs, give a talk on some of the work that they are doing there.

Needless to say, I can't go into the specifics of everything that was said (nothing about laser pens though!) but I can talk about one feature that has made it into Qlik Sense - global search.

I particularly wanted to talk about this one feature because I am not sure that enough has been said about it - probably because most people who are experienced with QlikView might miss the significance of it.

In the Search Object in QlikView, you can enter multiple terms.  It will then highlight where all of those terms might match in the different fields.  There is no concept though about how well those search terms have matched - do they all match, if not how many match?  If I type the query light beer minnesota into the object, then I might get some hits across some fields, but I don't really understand how well I have searched.  I don't know if there is any association between the values that have been found - and Qlik is all about association.

In Qlik Sense, however, the same query gives me far more interesting results.  It gives me qualitative information about how many hits that I have had, what fields I have hit, and how they associate together.  It also gives me information on places where I didn't have exact hits, but still might be very useful to know about.

This might appear to be quite a simple thing, but it is really very powerful.

Users are already used to asking questions of Google to find information.  In the future, they may expect to ask similar questions from their own data to discover insight.  Perhaps even verbally!

Watch this space...

---

Stephen Redmond is author of Mastering QlikView, QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a Qlik Elite Partner.
 

Mini chart for alerts, text objects and popups

I was handed and interesting challenge yesterday.  The challenger was looking for a way to present both a list of dimensions, and an associated calculation, in a popup dialog.  This sounds like it should be quite achievable, but I couldn't think of an answer immediately!

We can, of course, present the list of dimensions using Concat:

=Concat(Customer, chr(10))

We can add a sort

=Concat(Customer, chr(10), -Aggr(Sum(LineValue),Customer))

If we tried to add the Sum into the Concat's first parameter, we would get an error about not allowing an aggregation.

We were racking our brains to come up with a way to add the value (without seeing the bleeding obvious! - see below).  Eventually, I came up with this horrendous expression:

=If(Len(FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),1))>0, 
FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),1)
& ' - ' & Num(Sum({} LineValue), '#,##0')
& chr(10), Null()) 

& If(Len(FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),2))>0, 
FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),2)
& ' - ' & Num(Sum({} LineValue), '#,##0')
& chr(10), Null()) 

& If(Len(FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),3))>0, 
FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),3)
& ' - ' & Num(Sum({} LineValue), '#,##0')
& chr(10), Null()) 

& If(Len(FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),4))>0, 
FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),4)
& ' - ' & Num(Sum({} LineValue), '#,##0')
& chr(10), Null()) 

& If(Len(FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),5))>0, 
FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),5)
& ' - ' & Num(Sum({} LineValue), '#,##0')
, Null())

This just shows the top 5 - but only if there is no match on the expression - in which case it would hide the matching values (because firstsortedvalue returns null).  So I had some thoughts and came up with this:

=If(Len(FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),1))>0, 
FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),1)
& ' - ' & Num(Sum({} LineValue), '#,##0')
& chr(10), Null()) 

& If(Len(FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),2))>0, 
FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),2)
& ' - ' & Num(Sum({} LineValue), '#,##0')
& chr(10), Null()) 

& If(Len(FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),3))>0, 
FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),3)
& ' - ' & Num(Sum({} LineValue), '#,##0')
& chr(10), Null()) 

& If(Len(FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),4))>0, 
FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),4)
& ' - ' & Num(Sum({} LineValue), '#,##0')
& chr(10), Null()) 

& If(Len(FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),5))>0, 
FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),5)
& ' - ' & Num(Sum({} LineValue), '#,##0')
& chr(10), Null())

& If(Len(FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),6))>0, 
'Others'
& ' - ' & Num(Sum(LineValue)-Sum({} LineValue), '#,##0')
, Null())

This adds a small rand() value that appears to break the match enough to make things work - I am not sure if it will work on all occasions, but in several tests it did work.  So, it does solve a problem where I want to see just the top 5 and "Others".

But it doesn't show all the dimensions plus values - which was the original challenge.

A rethink was in order and I returned to the Concat with the sort.  I wonder...  If the sort expression accepts an Aggr, would that be allowed in the first parameter:

=Concat(Customer & ' - ' & Num(Aggr(Sum(LineValue), Customer), '#,##0'), chr(10), -Aggr(Sum(LineValue), Customer))

Yay!  It worked!!!

Potentially, this could be used in text objects, alert texts, and popup texts.

---

Stephen Redmond is author of Mastering QlikView, 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

No nodistinct

This article is taken from my new book - Mastering QlikView.  Available from 26th November.

---

The Aggr function has, as an optional clause, the possibility of stating that the aggregation will be either distinct or nodistinct.

The default option is distinct and, as such, is rarely ever stated.  In this default operation, the aggregation will only product distinct results for every combination of dimensions - just as you would expect from a normal chart or straight table.

The nodistinct option only makes sense within a chart, one that has more dimensions than are in the Aggr statement.  In that case, the granularity of the chart is lower than the granularity of the Aggr and therefore QlikView will only calculate that Aggr for the first occurrence of lower granularity dimensions and will return null for the other rows.  If we specify nodistinct then the same result will be calculated across all of the lower granularity dimensions.

That can be difficult to understand without seeing an example, so let us look at a common use case for this option.  We will start with a data set:

ProductSales:
Load * Inline [
Product, Territory, Year, Sales
Product A, Territory A, 2013, 100
Product B, Territory A, 2013, 110
Product A, Territory B, 2013, 120
Product B, Territory B, 2013, 130
Product A, Territory A, 2014, 140
Product B, Territory A, 2014, 150
Product A, Territory B, 2014, 160
Product B, Territory B, 2014, 170
];

We will build a report from this data using a pivot table:

Now, we want to bring the value in the Total column into a new column under each year, perhaps to calculate a percentage for each year.  We might think that, because the total is the sum for each Product and Territory then we might use an Aggr like this:

   Sum(Aggr(Sum(Sales), Product, Territory))

However, as stated above, because the chart includes an additional dimension (Year) than the Aggr, then the expression will only be calculated for the first occurrence of each of the lower granularity dimensions (in this case, for Year = 2013):

The commonly suggested fix for this is to use the Aggr without the Sum and with a nodistinct like this:

   Aggr(NoDistinct Sum(Sales), Product, Territory)

At first, this will appear to solve the problem:

The problem occurs when we decide to have a total row on this chart:

Because there is no aggregation function surrounding the Aggr, it does not total correctly at the Product or Territory dimensions.  And we can't add an aggregation function - like Sum - because it will break one of the other totals.

There is, however, something different that we can do - something that doesn't involve Aggr at all!  We can use our old friend Total:

   Sum(Total<Product, Territory> Sales)

This will calculate correctly at all the levels:

---

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

Easy incremental load

I was demoing a quick incremental load script earlier today so I thought I would share it here.

To generate the test data, I used an echo command at the Command Prompt to generate data using the system time:

echo %DATE% %TIME% %RANDOM% >> Data.txt

Every time I run this command it writes a new line to the text file with the current system date and time and a random value.  Perfect for demoing an easy incremental load.

Here is the code:

// vLastLoadTime holds the last date of loading
// If it is not set, then set it to 1,000 days ago
If Len('$(vLastLoadTime)')=0 Then 
Let vLastLoadTime=Num(Now()-1000);
End If 

// vLoadTime holds the current run time
Let vLoadTime=Num(Now());

// Load the data from the data source
// between the two dates
Data:
LOAD @1:23 as DateTime, 
     @24:n as Value
FROM
[..\Data Files\TXTs\Data.txt]
(fix, codepage is 1252)
Where @1:23>$(vLastLoadTime)
And @1:23<=$(vLoadTime);

// If there were any rows,
// concatenate the QVD rows and re-Store
If NoOfRows('Data') > 0 Then

// Get the length of the QVD file
Let vFileLen=FileSize('Data.qvd');

// If the file exists, load it
if Len('$(vFileLen)')>0 Then

Concatenate (Data)
Load
DateTime,
Value
From [Data.qvd] (qvd);

End if

// Store the full table back to QVD
Store Data into [Data.qvd];

// We can drop the table
Drop Table Data;

End if

Let vLastLoadTime=vLoadTime;

// Load the data from the QVD
Data:
Load
DateTime,
Value
From [Data.qvd] (qvd);

Of course, this is just one type of incremental load that we can do using QVDs.  There is an excellent article in the QlikView help file that describes other scenarios.  It is worth reading.

---

Stephen Redmond is author of Mastering QlikView, 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

Promote a Qlik Sense sheet from Community to Approved

In Qlik Sense, users (with appropriate permissions) can create their own sheets.  These sheets will not be seen by any other users unless the owner chooses to publish them.  Once published, the sheet goes into the Community sheets area.

It may be the case that a sheet created by a user is so good that we would like to promote it to being an Approved sheet.  Right now, there doesn't appear to be a supported method of doing so.

We can, however, use an unsupported method and that is simply to update the entry in the database for the Sheet object.  You will need to refer to my previous post on connecting to the repository and use a Query tool (such as the one that comes with pgAdmin III).

The table that we want to update is AppObjects.  You will need to write a query (or load the data into Qlik Sense!) to find out the correct ID.  The Object will have Published=true and Approved=false so we just need to update the latter:

Update "AppObjects"
Set "Approved"=true
Where "ID"='F3986BDB-1BD7-41D8-8B27-C5040103B827'

Simple as that.  Once is is approved, as well as appearing in the Approved list, if you duplicate the application, the sheet will appear in the available sheets to edit.

---

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

Configure a Qlik Sense security rule to reload one task

One of the cool things about Qlik Sense Server is the ability to be far more granular as to what you give particular users access to do in different areas.  In QlikView Server, we can make people Document Administrators and give them access to a folder to be able to perform reload tasks on the documents there.  In Qlik Sense Server, we can give a user, or users, access to just one reload task for one application.

In Qlik Sense QMC, I will go to the Security Rules section and add a new rule.  I will probably only want to add Read and maybe Update but not allow Delete for this rule.  The rule will apply to QMC only.

The Resources that I will list here can be either specific names of objects or they will be wildcards.  In this example, my list of Resources are:

QmcSection_Task - the access to the Task section of QMC
ReloadTask_1899c8cb-3073-4362-9f98-a36dee86dcc8 - Access to a specific task
App_3a1be6fa-99b4-422c-94a8-de031c94a7f3 - Access to a specific application

I could give access to ReloadTask_* and App_* to give the user or group access to all tasks and all apps.

In the access, I can assign to a specific set of users or I can specify a name of a role.  This role does not have to exist anywhere else - this is you creating it!

So, how do I find out the Guid of a particular Task or App?  Well, I could query the database directly for the information (see my previous blog entry - Connect to your Qlik Sense repository).  Alternatively, I can use the web browser's developer tools to help:

If I right-click on a Task or App in their respective lists, I can select "Inspect Element" from the menu (IE11 and Chrome - other browsers may vary).  The Span that contains the row will have an element that contains the Guid:

Just copy and paste!

---

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

Connect to your Qlik Sense Repository

There are many reasons why you might want to connect to your Qlik Sense repository.  For example, you may want to create a Qlik Sense application that lists all the applications and objects, along with current publication status.

It is quite easy to connect as it is hosted, by default, in a PostgreSQL database.

First thing that you will need is the download of the PostgreSQL ODBC drivers from: http://www.postgresql.org/ftp/odbc/versions/msi/ - there are both 32 bit and 64 bit versions available.

Once you have installed the driver, you can configure the ODBC connection:

The things that you will need to know are:

Database - QSR
Server - wherever the repository database is installed.
Port - 4432 (not the default of 5432)
User Name - postgres
Password - the password that you provided on installation.

That should be good to go.

For a good tool to browse the database, pgAdmin III is a winner: http://www.pgadmin.org/

---

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

Update to Link Table rules

Back in 2011, I wrote a blog post entitled "Rules for creating a Key/Link Table in QlikView".  There were 3 basic rules:

1. Create a primary key in all the tables,
2. Rename the foreign keys to break the links,
3. Use a mixture of Concatenate and Join to generate the Key table using the Resident data.

These rules will still create you a Key/Link table that will work, but it might not always be the most efficient.  As I have had to deal with larger and larger data sets in QlikView, I have learned the hard way that things that work quite well on a table with a few hundred thousand records do not work quite as well when your tables with a few hundred million.

It is really the first "rule" here that can be the problem.  In a dimension table, the key that associates to the Key/Link table does need to be the primary key, however, the key that associates fact tables to the Key/Link table does not have to be a primary key!  This key only needs to be unique for the set of keys being linked to in the Key/Link table, not for the whole record.

For example, if my fact table contains CustomerID and DateID, most of the time that combination will not make a primary key in the fact table, but it is all the key that we need to associate to a Key/Link table containing CustomerID and DateID.  By not using a primary key in the fact table, we are vastly reducing the cardinality of the key being used and hence the amount of memory required to store it.

Another change that I would make is that I no longer use AutoNumberHashxxx to generate a key value.  Now, I always use AutoNumber with an AutoID.  This is because AutoNumber with an AutoID will generate a set of sequential integers which do not, in general, get stored in symbol tables, further reducing the memory requirement.  Using AutoNumberHashxxx to generate multiple different keys will result in non-sequential integer key values which will have to be stored in Symbol tables.

Yet another change that I would require is that the Key/Link table is generated using the Distinct keyword.  Nothing kills performance quite like having duplicate key values in a Key/Link table.

Finally, I no longer require to rename the foreign keys and then rename them back when creating the Key/Link table - I just leave them as is and then use the Drop Field xxx From yyy statement to remove the old foreign keys.

So, the new rules become:

1.  Create a key in each table at the correct granularity to associate to the Key/Link table.  Use AutoNumber with an AutoID.

2.  Use a combination of Concatenate, Join and Distinct to load a Key/Link table of distinct values.

3.  Use Drop Field xxx From yyy to remove the old foreign keys from the fact tables.

For example:

I have a simple structure with Customer, Calendar, Orders.  I have a separate fact table with Customer, Calendar and Delivery information.  I get a synthetic key that I want to resolve by using a Key/Link table:

Step 1 - Customer and Calendar (DateID) already have a primary key.  In Order I will create an ID from CustomerID and DateID.  I will do the same in Delivery:

   ...
   AutoNumber(CustomerID & '-' & DateID, 'CD') As LinkID,
   ...


Step 2 - Now I load my key table.  I will begin with data from the Order table:

   Key:
   Load Distinct
LinkID,
CustomerID,
DateID
   Resident
Order;

Now Join in the Product and OrderDetail keys from the OrderDetail table.

   Concatenate(Key)
   Load Distinct
LinkID,
CustomerID,
DateID
   Resident
Delivery;


Step 3 - Drop the old foreign keys:

   Drop Fields CustomerID, DateID From Order;
   Drop Fields CustomerID, DateID From Delivery;

Now all should be good:

Of course, we don't just create link tables to avoid synthetic keys, we usually create them to overcome association difficulties such as loops.

There is a special prize of a free copy of my forthcoming book, Mastering QlikView, for anyone who can give me a good, technical (i.e. not "because Qlik say so"), reason as to why the original, synthetic key containing, data structure is less desirable than the link table structure.

---

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

Extensions in Qlik Sense

Qlik Sense is all extensions.  Even the out of box objects are extensions.  It also comes with some samples of additional Extensions.

After installing Qlik Sense Desktop, have a look in %userprofile%\Documents\Qlik\Examples\Extensions.  You will find several sub-folders, each containing one extension.

If you have experience with QlikView 10/11 extension objects (or have read my blog entries on how to create extensions) then you will see immediately that there is a different way of creating extensions in Qlik Sense.

There are two core files that you must have:

- a .QEXT file which contains the JSON description of the extension that will be used within the desktop client.
- a .JS file that contains the javascript to implement the extension.  This JS is build around the requiredjs framework.

Additional files can be added as required.

If you copy one of the extension folders into %userprofile%\Documents\Qlik\Sense\Extensions and restart the Qlik Sense Desktop (F5 may be enough) then the extension should appear.

You can also simply create your own sub-folder and create the .QEXT and .JS files and they will be picked up.  However, it is easier to use the Workbench to create your extension from a template.  To run the Workbench, first make sure that Qlik Sense Desktop is running (it provides the web service for this) and then connect to http://localhost:4848/workbencheditor.

There is no documentation for the extensions in the default help for Qlik Sense, but you may be able to access the beta documentation at: http://betahelp.qlik.com/0.95/en-US/online/index.html.

Some of you may have seen a post of mine where I had proposed a new chart type called a Pie-Gauge.  I had already created a QlikView v11 extension to generate these gauges using Raphaëljs to draw the pies so I wanted to have a go at doing this in Qlik Sense.  After a bit of trial and error, and looking at other extensions (including Ralf Becher's implementation of the d3 dependency wheel), I was able to get what I was looking for:

I am happy for anyone to grab the code for this from my github repository.  Just grab the whole RedmondPieGauge folder and drop it into your %userprofile%\Documents\Qlik\Sense\Extensions folder and restart the Qlik Sense Desktop.

You need to specify one dimension and then two expressions - one for Actuals and one for Target.

Each of the objects - including the text labels - are clickable to make selections exactly as if they were a Qlik Sense object.  This also implements a slider in the properties to allow you set a doughnut size.  Anyone who knows me will know that this was definitely an academic exercise - I hate doughnut charts!

Enjoy.

---

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

Qlik Sense

Yay!  It is finally here.  Qlik Sense, the product that has previously been called QlikView.next, has been released to the public as a free download.

http://www.qlik.com/us/explore/products/sense

This is the desktop version of the product and is licensed similarly to QlikView Personal Edition.  You can use it "...solely for the User’s personal or internal business use...".

Right now, there is no server version but this will follow.  Qlik have also promised a public server service - Qlik Cloud -  to allow users to create and share content.  This will be awesome when it happens.

So, now Qlik have two products - QlikView and Qlik Sense.  Qlik Sense is an evolution of QlikView, but QlikView will still be around for several years to come.

So, what is in this new Desktop client?

When you first open it, you arrive at the "Hub".  This lists all your applications.  You simply click on an application to open it.

When an application opens, it will allow you to select sheets (just like current QlikView).  The navigation between sheets is different, so will need some getting used to, but it is pretty straightforward.

Opening the Sales Management demo app invites you to look at the new Story feature, where you can find out more about Qlik Sense.  The sheets in this application allow you to try out creating new objects, and learn how easy it is.

If you do feel brave enough to create your own applications, there is a very useful feature that allows you to drop a file of structured data into the application and then it will generate the data model and allow you to get up and running.

If you feel really brave, you can go into the script editor!  As it turns out, not really a lot of bravery required as any experienced QlikView developer will be able to get up and running in here - the script syntax is all the same.  In fact, all your old scripts will run and generate a data model.

Your Qlik Sense apps, in the new QVF format, are stored in %userprofile%\Documents\Qlik\Sense\Apps.  If you drop an older QVW file into this folder and re-open (F5 works) the Hub, the app will appear and can be loaded into Qlik Sense.  When you save it then, it will be saved in the new format.  Right now, only the data and script will get converted into the new format, all UI will be lost.  There are also limitations around applications with hidden scripts and those with section access.

I am really happy to see this new version finally released.  It marks the start of a whole new Qlik journey and I, for one, am happy to be along for the ride.  I can't wait to see where it takes me.

---

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

Is QlikView v11 vs. QlikView.Next the right question?

I have seen and heard a lot of discussion about what is missing from the initial beta release of QlikView.next as compared with QlikView v11.  I am not sure that this is the right discussion to have.

The way QlikView has evolved, it has become much more than a data visualization tool.  It has become almost an application development platform.  All of us have used QlikView to do application things - show/hide objects, run macros, actions, etc. - and have built some excellent applications for our customers.  But was this the right thing to be doing?  Did we do it in QlikView because it was the right place to do it, or because we could and QlikView made it easy for us to do?

Let's think for a minute about the strengths of QlikView 11. For me, they are:

• A really good ETL script to allow us take data from lots of different places and combine them in different ways - including using best practice dimensional modelling.

• A best of breed analytics engine that calculates fantastically well across large data sets.

• Associative logic - the green, white and grey, awesome functionality for data discovery.

• A good (but not awesome) data visualization tool.

The first 3 are, for me, the magic that makes QlikView the fantastic tool that it is, the one that really differentiates it from its competitors.

You will notice that I don't mention "application building" as a strength in QlikView - that's because it isn't a strength.  If I want to build an application, I will use Visual Studio, JDeveloper, or even Notepad++, but I am not going to use QlikView.  If I want something that is going to have application functionality - like writing to a database for example - I am not going to think of using QlikView for that, even if I could hack something together.

I can include QlikView data in another application, and I have built several such applications.  I can also create an application inside of QlikView using an extension.  I can do both of these even easier in QlikView.next, but still using my usual application building tools.

So, from my point of view, QlikView.next still has all of the great things that QlikView 11 has - except it is even better as it now is an AWESOME data visualization tool, one that business users will love using.

I think that for new customers, this will be fantastic. I think that they will start out building QlikViews that are designed around data discovery and their users will love it.

For existing customers, there is a different perspective.  Many will have invested a lot of money in building what they have.  My thinking is that it is an excellent opportunity for them to evaluate what they have done and revisit the design decisions that were made to see that they can do it better - while knowing that their existing platform will be supported for several years to come.

I think that if you look at the beta (note *beta* - not shipping product) and dismiss it because it can't do this or can't do that, then you might be missing a trick.  Think instead of what you can do - there is a wonderful world of opportunity opening.

---

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

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!):

BatFile:
Load
'REM This is a batch file test' As [@echo off]
AutoGenerate(1);

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

BatFile:
Load 
'MOVE $(vFile) C:\Temp\Folder2' As [@echo off]
AutoGenerate(1);

Next

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

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

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: https://www.youtube.com/watch?v=vJG698U2Mvo

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

#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: http://twubs.com/QlikTips, 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

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

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