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, SalesUSA, San Diego, 23712USA, Dallas, 49888USA, New York, 71621USA, Boston, 55033UK, London, 13444UK, Birmingham, 31212UK, Manchester, 39712Germany, Berlin, 49100Germany, Frankfurt, 59102Germany, Munich, 41568Japan, Tokyo, 41412Japan, Yokohama, 55554Japan, Osaka, 12345];
Then we need to create a Pivot Table with Country and City as Row Dimensions and the following 3 measures:
| Label | Expression |
|---|---|
| Sales $ | Sum(Sales) |
| Total Sales % | Sum(Sales)/Sum(TOTAL Sales) |
| % Sales by Country | Sum(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 QlikView, QlikView for Developer's Cookbook and QlikView Server and Publisher.
Follow @stephencredmond
