Sunday, 29 November 2020

Revisiting the QlikView Cookbook - Labels on a Line Chart

It has been 7 years since the QlikView for Developer's Cookbook was published! 

The book, which was very much targeted at QlikView developers, is still available. Just for fun, I thought that it might be interesting to write a series of posts looking back at some of those recipes and seeing how they could be recreated in Qlik Sense Business.

In this post, I'll look at the recipe on Replacing the legend in a line chart with labels on each line.

The idea here is that the legend on a chart can be somewhat removed from the data and not always easy to match the colours to the lines, especially for those with a visual impairment such as colour blindness.

The trick here is to use a small hack with the Dual function to only make labels printable for the last value of the line. Here is a quick run-through of the method - which isn't a million miles away from the QlikView method presented in the book.

Start with some data:

Date, USA, UK, Japan, Germany
2013-01-01, 123, 100, 80, 40
2013-02-01, 134, 111, 75, 50
2013-03-01, 155, 95, 70, 60
2013-04-01, 165, 85, 88, 50
2013-05-01, 154, 125, 90, 70
2013-06-01, 133, 110, 75, 99

Now, we can quickly create a simple line chart by Date and Country as in the image above, with a measure of Sum(Sales).

Now, if we edit the measure to this:

  If(Date=Max(total Date), Country, ''),

So here we are checking if the current date matches the maximum date. If it does, return the name of the Country. If not, return a blank string.

Before we change anything else, we should change the measure's Number formatting option to be Measure expression, otherwise Qlik Sense will override our Dual and apply a default numeric formatting.

Now, down in the Appearance section, under Presentation, we turn on the option to Show data points. Set the Value labels option to Auto.

Just below this, in Colors and legend, turn off the Show legend option - we don't need it!

If all has gone to plan, we should have a line chart that is something like this:

This will not be the perfect solution in every circumstance. If you have very many lines then it just won't look great. But it is something worth playing with, and certainly a hack worth knowing about.

As well as holding a Master's Degree in Data Analytics, Stephen Redmond is a practicing Data Professional of over 20 years experience. He is author of Mastering QlikViewQlikView for Developer's Cookbook and QlikView Server and Publisher.