Revisiting the QlikView Cookbook - Using OMIT

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 Blocking user access to a field using OMIT.

Picking this one was spurred by a message last week from a good friend who reminded me of a blog post that I wrote on the topic back in 2011!

Privacy of fields have been around longer than GDPR or and other legislation. In the Cookbook, I imagined a scenario of a HR application that contained salary and absence information that we might want to restrict access to. I am sure that we can all imagine many other fields that we might want to restrict - including names!

Anyway, here is the code setup to replicate the recipe in Qlik Sense Business:

Section Access;

Access:

LOAD * INLINE [

  ACCESS, USER.EMAIL, OMITGROUP

  ADMIN, stephen.redmond@notmymail.fake, 

  USER, user1@notmymail.fake,

  USER, user2@notmymail.fake, SALARYONLY

  USER, user3@notmymail.fake, SALARYANDABSENCE

  USER, user4@notmymail.fake, ABSENCEONLY

];

OmitGroups:

LOAD * INLINE [

  OMITGROUP, OMIT

  SALARYONLY, Salary

  SALARYANDABSENCE, Salary

  SALARYANDABSENCE, Absence

  ABSENCEONLY, Absence

];

Section Application;

Employees:

LOAD * INLINE [

  EmpID, Name, Salary, Absence

  1, Joe Bloggs, 50000, 3

  2, Jane Doe, 45000, 5

  3, Fred Frank, 30000, 0

  4, Jeri Jublek, 19000, 12

];

The blank values for the ADMIN and user1 here are deliberate. They could really be anything - blank, "NONE", "MISSING", etc. - anything that would not connect these users to the OmitGroups. The effect here is that these users will not have any OMIT entries so will see all the fields - as in the image below:

For the users with the SALARYONLY restriction, they cannot even see the Salary field. It will appear as if the field does not even exist in the data model:

For those with SALARYANDABSENCE, they will be missing both fields:

In our post-GDPR world, OMIT is is feature of Qlik that you should definitely consider when you have PII or sensitive fields involved. Make sure to be explicit about the users who can and can't see these data.

---

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 - Rounding Time to Quarter Hour segments

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 Consolidating a date-time value into quarter hourly segments.

The premise behind this recipe is one that is relatively straightforward, we would like to round down Time Stamp data to a less unique value. The best reason for doing this is to simply remove the uniqueness of the data - Time Stamps could represent millions of values in a day, because they can be sub-second, when there is really only 86400 seconds in one day, 1440 minutes and 96 quarters. The less unique your data, the better that Qlik can store it. Of course, you need to bear in mind the business need for the presentation of the values - if they don't need minutes and seconds, don't store them!

The simple load statement for this recipe is:

Load
  TimeCounter,
  TimeStamp(TimeStamp) as Date,
  WeekDay(TimeStamp) As WeekDay,
  TimeStamp(Round(TimeStamp, (1/(24))), 'M/D/YY HH:mm') As Hour,
  TimeStamp(Round(TimeStamp, (1/(24*4))), 'M/D/YY HH:mm')
  As QuarterHour;
Load
  Today() + (Rand()) As TimeStamp,
  1 as TimeCounter
AutoGenerate(1000);

This does give us a result which may be good for our purposes. However, we need to bear in mind that this is a mathematical rounding, and that may not match business rules that we have to abide by. In this example, we can see that the 06:00:00 hour covers time slots from 05:30:00 to 06:29:59. The 06:15:00 quarter covers times slots from 06:07:31 to 06:22:29. These ranges may not be what a business user would expect - would you expect 5:30am to appear in the 6am group?

If you want your 06:00 to be 06:00:00 to 06:59:59, then this might work: 

TimeStamp#(Text(TimeStamp(TimeStamp, 'YYYYMMDD HH'))&':00','YYYYMMDD HH:mm') As Hour

---

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

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:

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