Sunday 6 December 2020

Revisiting the QlikView Cookbook - Using TOTAL

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 using TOTAL to calculate the percentage of total and subtotal.

I like this recipe because it really helps to understand the power of the TOTAL function, which has been a core part of Qlik for a long time, pre-dating Set Analysis for use in vertical calculations.

We start off with a simple set of Sales data:

LOAD * INLINE [
Country, City, Sales
USA, San Diego, 23712
USA, Dallas, 49888
USA, New York, 71621
USA, Boston, 55033
UK, London, 13444
UK, Birmingham, 31212
UK, Manchester, 39712
Germany, Berlin, 49100
Germany, Frankfurt, 59102
Germany, Munich, 41568
Japan, Tokyo, 41412
Japan, Yokohama, 55554
Japan, Osaka, 12345
];

Then we need to create a Pivot Table with Country and City as Row Dimensions and the following 3 measures:

LabelExpression
Sales \$Sum(Sales)
Total Sales %Sum(Sales)/Sum(TOTAL Sales)
% Sales by CountrySum(Sales)/Sum(TOTAL<country> Sales)

Set the display format of the two percentage measures to an appropriate % format. Turn Show totals on for the Country dimension.

When the pivot table first displays, the Country dimension will need to be expanded to show the cities.

Hopefully it is straightforward to see what has happened here. Total by itself tells the function to ignore the dimensions in the chart. Therefore the Total Sales % calculation takes each individual sales total and divides it by the overall total. So, for Berlin, that is 49100 / 543703 = 9%.

When we include one, or several, dimension within the angle bracket after the TOTAL, we tell Qlik to ignore all dimensions in the chart, except for the ones lists. So when we include Country in the angle brackets, it will give us the Sub-Total for that country. Again, for Berlin, that is 49100 / 149770 = 32.8%.

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.