Wednesday 9 December 2020

Revisiting the QlikView Cookbook - Dynamic part-to-whole Pie 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 Creating a dynamic part-to-whole pie chart.

Now, I know that pie charts are not everyone's first choice, but for part-to-whole comparisons they work just as well, if not better, than other alternatives. (If interested in more on this, read my IEEE VIS 2019 paper, Visual cues in estimation of part-to-whole comparison here).

Probably most will agree that this style is not the best:


The large number of segments here tempts the user away from part-to-whole into part-to-part comparisons. There are argument that say this would be equally as useful as a bar equivalent, but I won't make them here.

To setup an example in Qlik Sense Business, I loaded the following data:

LOAD * INLINE [
Country, Sales
USA, 53453
Mexico, 21317
Canada, 14545
UK, 12333
France, 21333
Germany, 23123
Japan, 44342
China, 34234
Australia, 12345
];

The setup of the dimension is quite important. Instead of using the normal field as a dimension, I use a calculation:

=if(
  Aggr(Sum(1), Country)>0,
  'Selected: $(=Concat(DISTINCT Country, ', '))',
  'Others: $(=Concat({<Country=E({$})>} DISTINCT Country, ',
  '))'
)

It is using Aggr to group together Countries that are selected separately from non-selected. We then need to setup the Measure expression to ignore selections on Country:

Sum({<Country=>} Sales)

Now, when we make a selection on Country, the pie chart won't drill to that selection, it will show us that selection versus the other Countries:


It is true part-to-whole!



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.  
  LinkedIn