Tuesday, 16 December 2014

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.
Follow me on Twitter   LinkedIn

Friday, 12 December 2014

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.
Follow me on Twitter   LinkedIn

Friday, 5 December 2014

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.
Follow me on Twitter   LinkedIn

Friday, 28 November 2014

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 QlikViewQlikView 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, 19 November 2014

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

Tuesday, 4 November 2014

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 QlikViewQlikView 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

Saturday, 18 October 2014

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