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:

CrossTable(Country,Sales)

LOAD * INLINE [

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:

Dual(

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

  Sum(Sales)

)

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 QlikView, QlikView for Developer's Cookbook and QlikView Server and Publisher.  
Follow @stephencredmond  

Making Sense of Themes

Episode 14 of the Qlik Virtual Meetup Scotland (QVMS) had some very interesting speakers. Old friend and fellow Luminary, Barry Harmsen, from Bitmetric, was talking about his sensetheme.com site, so I thought that I might delve a little into the idea of themes and explain why I think it might be a good idea for you to explore them.

For some historical context, the idea of Themes is not new to Qlik. Anyone who has used QlikView will know to what level of detail you can specify colours, various fonts, borders, shadows, etc. on various objects and then use the Theme Maker function to export those settings into an XLM file for use in other documents.

When Qlik Sense was first released in 2014, it didn't have too much in the way of ability to change things. You got the plain white background and the default set of fonts. You could change the odd colour, but not much else.

The Qlik Sense "Classic" theme

Many folks grumbled at this and Qlik listened. Starting from Qlik Sense (on-premise) February 2018 release, custom themes could be used to style an app. This feature has subsequently been rolled out on SaaS offerings.

Out of box on the current version of Qlik Sense Business, there are four themes available. Sense Focus is the default, but you can also choose Sense Breeze, Sense Horizon, or the "Classic".

Sense Focus - the default theme

Sense Breeze theme

Sense Horizon theme

A theme is simply a JSON file with a collection of settings. If you are comfortable working in JSON and with CSS properties, then a good place to start is the Getting started building custom themes page in the Qlik documentation. If you are less comfortable, then Bitmetric's Sense Theme tool is another excellent choice.

For free, you can download a load of different examples, from Classic QlikView to Tableau! Also for free, with some limitations, you can create your own theme file and use their friendly UI to help you setup the colours.

For a small monthly fee, you can unlock more features and get rid of those pesky pop-up ads. I can see many businesses taking this option as they will want to apply individual theme elements to their Qlik Sense applications, and this will become a valuable tool for their designers to work with.

Time to dive into themes?

---

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 QlikView, QlikView for Developer's Cookbook and QlikView Server and Publisher.  
Follow @stephencredmond  

Revisiting the QlikView Cookbook - Boxplot

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 Creating a box plot chart for a simple data set.

The Boxplot in Qlik Sense is a little more advanced than the one that I originally wrote about in QlikView. It has an element of sophistication around it which may actually initially confuse a user, especially one who has been used to other statistics tools. When I use a Boxplot in QlikView, I generally expect to add one dimension, and then expect that the values will be presented for each value in that dimension. I can, of course, get quite sophisticated with my calculations, and there is even a Wizard to help me get started with creating the necessary AGGR expressions I may need.

The Qlik Sense Boxplot will actually assume that I want to do the calculations using similar AGGR expressions and, if I don't, I need to make some manual edits. It is probably easier if you let me show you what I mean with an example. First, some sample data - only slightly more complex than in the original recipe:

Load 
    'USA' as Country,
    Ceil(Rand()*12) as Month,
    Round(Rand()*50, 0.01) as Value
AutoGenerate(100);
Load 
    'UK' as Country,
    Ceil(Rand()*12) as Month,
    Round(Rand()*20, 0.01) as Value
AutoGenerate(100);

After loading, if I drop a Boxplot onto my sheet, it offers me the usual Add dimension and Add measure options. If I try to recreate the original QlikView Cookbook chart, I might try with Country and Value - actually you will note that, as usual, it forces you to add an aggregation.

Ok, so what happens next? Well, you might be surprised to see only 1 skinny box plotted!

So what happened? Why did it not split by Country as a QlikView user might have expected? Turning to the help file, you might find this description of adding the first dimension:

    This is the inner dimension, which defines a box.

Eh? What box does it define? Well, we have added a dimension and an expression, so this is the box it defines:

So, actually, there are only 2 values being represented by the Boxplot. The plot isn't looking at all the individual values (which can't be bigger than 50 because of the way we have created them), it is representing the statistics of all the calculated values - in this case only 970.77 and 2529.77.

Let's rejig slightly. I am going to replace the Box dimension with Month and set the X-axis dimension to be Country. The result looks something like this:

In this case, the "box" is now a table of the Sum of Value by Month, and there is a different "box" for each Country. For example, the UK "box" may look like this:

The Boxplot calculations are then calculated across the 12 values in each "box". Simples!

But what are the calculations? Well, if we look into the Box plot element properties, we can see that it defaults to some presets from which we can choose Standard (Tukey), Percentile-based or Standard deviation. If we turn off the Use-presets option, we can now see what set of calculations are being used for each element in the Boxplot. In fact, this also allows us to put in our own calculations! We could, for example, use this option to go back to a version of the original QlikView Boxplot.

If add a new Boxplot on the screen and then go straight to the properties and add Country for both the Box and X-axis dimension. For the Y-axis measure, I am just going to add the value 0 - it doesn't matter too much what value you use, though it does need to return a valid calculation or the chart won't work.

Now, when I go down to the Box plot element section and deselect the Use-presets option, I can specify new expression values:

Property	Expression
First whisker	Min(Value)
Box start	Fractile(Value, 0.25)
Center line	Fractile(Value, 0.5)
Box end	Fractile(Value, 0.75)
Last whisker	Max(Value)

Now we have something that is similar to the original!

---

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 QlikView, QlikView for Developer's Cookbook and QlikView Server and Publisher.  
Follow @stephencredmond  

Revisiting the QlikView Cookbook - Tooltips

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 first post, I'll look at the very first recipe - Creating custom pop-up labels on a bar chart.

In QlikView, it was necessary to manually put together a text expression to join multiple values, with their functions to ensure correct formatting, together and ensure values appear on separate rows using line feeds (using the chr function) - something like this:

= Country 
& chr(10) 
& 'Sales : ' & Num(Sum(Sales), '$(MoneyFormat)') 
& chr(10) 
& 'Costs : ' & Num(Sum(Costs), '$(MoneyFormat)') 
& chr(10) 
& 'Margin : ' & Num(Sum(Sales)-Sum(Costs), '$(MoneyFormat)') 
& chr(10) 
& 'Margin % : ' & Num(1-(Sum(Costs)/Sum(Sales)), '0.0%')

In Qlik Sense, it is a little easier. 😊

In most of the major chart types there is a sub-property in the Appearance property called Tooltip.

In here we can see the following options:

Option	Description
Hide basic rows	This either shows or hides the default Tooltip values. Useful to turn off if you are doing something custom.
Description	An expression that defines a piece of text to present within the tooltip. Could be used to present useful information for users such as how these metrics compare with others.
Measures	One or several measures to display. Each measure has typical sub-properties for measures such as label and numeric format.
Images	An image to display in the Tooltip - either a static one from the Media libaray, or from a Url (which could be dynamic!)

So, to recreate the manually constructed text from QlikView, we just need to turn off the Hide basic rows option, leave the Description blank, and add four Measures with appropriate formats. We might end up with a result like this:

---

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 QlikView, QlikView for Developer's Cookbook and QlikView Server and Publisher.  
Follow @stephencredmond