Time to say goodbye ... to the Qlik Luminary Program

It has been a blast!

Back in 2013, Qlik launched something called The Qlik Luminary Program with 10 "Charter" members drawn from some of their larger customers.

At the time, to be honest, I didn't really pay a lot of attention to it. I didn't know any of the charter members, and I didn't really know what the program was for, or even how to get involved with it. I was also pretty busy with other stuff! As well as spending my days pretty much working all the time with QlikView, and leading a team of QlikView developers, I had just published the QlikView for Developers Cookbook that June and was working on QlikView Server and Publisher.

What I didn't know was that Qlik were planning to anoint a set of people from their ecosystem to be Qlik Luminaries, and had polled their own people for candidates. It came as a pleasant surprise to me when I heard from Miguel Angel Baeyens de Arce, then an Enterprise Architect at Qlik, that he had nominated me and that I had made the grade!

First Luminary certificates signed by former Qlik CEO Lars Bjork

At Qonnections 2014 the new class of Luminaries were well feted. It was great to make a lot of new friends there, especially Torben Seebach, with whom I pounded the Orlando sidewalks at ungodly hours before the sun came up to make it too hot to run. It was a great week. 

Luminary Selfie 2014

We had an equally good time later that year when attending the Qlik Sense launch in Barcelona - where our Luminary status so overawed Brian Booden that he decided to spill his drink on me!

That was sadly the last big Qlik event that I got to attend for a while. Not long after publishing Mastering QlikView, I moved to work for Deutsche Bank - one of Qlik's large customers. It is fair to say that I had some good supporters within Qlik because, despite a drop off in social media activity and blogging, I managed to do enough to maintain my Luminary status through my period there.

My next job move moved my back to the Partner ecosystem, joining one of Qlik's biggest global partners, Accenture. I was luckily able to get back to Qonnections in 2018, and it was great to meet all my Luminary friends again. 

Luminary Selfie 2018

A big highlight of that year was the couple of days in Lund at the Luminary meetup at Qlik's original headquarters.

Qlik HQ in Lund

A change of role within Accenture in 2019 saw me actually move back to being a Qlik customer again as I am now part of Applied Intelligence team within Accenture's Global IT group, who own and run a very large Qlik infrastructure. We have a great team, especially my colleague Christine Afungchwi, who I was delighted to see joining the Qlik Luminary Program in 2020.

2020 saw a lot of changes for everyone, not least the move of Qlik World (formerly Qonnections) to an online format. Our Luminary meetup in the fall had to also go online, but the Program Team really knocked it out of the park with the setup and made it an event that I will never forget.

For me, 2020 also saw a big Qlik highlight where the Luminary Program team helped enable the Qlik Meetup community to go online using their WebEx connections. The very first one was the Qlik Virtual Meetup Scotland (QVMS), and I was extremely privileged that Brian, George and Andrew agreed to allow me to present at that meetup alongside Qlik's Elif Tutuk.

So now we are in 2021 and the Qlik Luminary Program is changing. They are renaming it to Qlik Luminary 50, reducing the number of members, and no longer accepting folks from the Qlik Partner ecosystem, restricting it to customer "advocates and counsellors". I think that after this length of time that it is probably right that there is a change, and it will be interesting to see what happens. But I will no longer be there.

I am in a strange position because I work for a Partner organization - Accenture regularly win the Partner of the Year accolade - but actually, because of my role in our Global IT group, I am a Qlik customer so probably could still apply. However, I think that it is time for me to retire.

While I am still passionate about Qlik, and still have some recognition as an expert across the ecosystem, it is no longer my full-time job as it was back in 2014. Although I am still an active user of Qlik tools - I have QlikView open most days and often help other teams with troubleshooting - most of my work these days is in PowerPoint and most of my team are working in Python. I never use any of the Qlik Data Integration tools - I have never needed to.

I haven't been active on Qlik Community for some time, though I remain reasonably active on social media, and have somewhat rejuvenated this Qlik Tips blog recently. But it is really just an occasional hobby as I am not working with the tools enough to generate good content for more regular updates.

Over the last year or so, as well as the QVMS, I have also presented at global industry and academic conferences, and local Qlik Meetups, where I have identified myself as a Qlik Luminary and shared my ideas and knowledge around data. I will continue to do that - except the Luminary part - as it is part of my DNA to share and educate. I suspect that most of that in future will not be Qlik related, but I will continue to apply to Qlik World to speak and share my ideas. I will continue to attend where I can. I will continue to blog here ad-hocly.

Presenting at the Qlik Meetup Dublin

I am committed to providing feedback that is constructive and actionable to Qlik, but that forum will be via our regular connects from within Accenture, where I will continue to advocate for Qlik use - not because I am a Luminary but because I believe it is a great product and we get great value out of using it.

I probably have a lot of the aspects that Qlik are looking for in their Luminary 50, but I don't think that I am going to be the Luminary that Qlik need at this stage of my career. However, I would still encourage other Qlik customers to think about applying to the new Program - I would love to see a diverse pool of people in the new club. Even if you don't think you match all the exact requirements, if you are a customer and you are passionate about Qlik, then send in an application!

I couldn't write a goodbye to the Luminary Program without mentioning three very special people: Viktoria Lindback, Carolyn Davis and Gillian Farquhar. You three have been nothing but helpful, generous and absolutely brilliant over the years and I hope that I can always call you my friends. I will always be available to you if you need help or advice.

In the words of the late, great, Douglas Adams, so long, and thanks for all the fish!

---

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 - Containers as Multiboxes

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 containers as an alternative to multiboxes.

For those who are unfamiliar with the QlikView Multibox, it was a way for us to add multiple filters into a relatively small area:

When a user clicked on the Dimension name, the relevant List box would pop out. This is not dissimilar to how the Filter pane works in Qlik Sense when there are several Dimensions added and not enough room to display values:

The Cookbook presented an alternate option whereby a Container is used to contain multiple List Boxes:

It worked quite well (in my humble opinion!)

Now, there is also a Container object in Qlik Sense, so we can achieve something similar. This is the load script from the Cookbook:

Data:
LOAD * INLINE [
  Country, Product, SalesPerson, Category, Sales
  USA, Widget, Joe, Widgets, 1234
  USA, Woggle, Joe, Widgets, 1983
  USA, Brogue, Jane, Footwear, 1175
  USA, Clog, Jane, Footwear, 1376
  UK, Widget, Tom, Widgets, 954
  UK, Woggle, Tom, Widgets, 953
  UK, Brogue, Tom, Footwear, 989
  UK, Clog, Tom, Footwear, 875
  Japan, Widget, Mike, Widgets, 1265
  Japan, Woggle, Mike, Widgets, 1345
  Japan, Brogue, Jane, Footwear, 1425
  Japan, Clog, Jane, Footwear, 1324
];

Loading this in Qlik Sense Business, we can then add a Container and 4 separate Filter Pane objects for the 4 Dimensions:

If you don't have too much real estate to devote to it, then it will automatically collapse to a version with a menu to select the Dimension:

I prefer this to the default Filter Pane method.

Worth noting that the default would be to have "Sales Person" twice - as a Title for the Container and for the Filter Pane - you can blank out the Filter Pane's title to make it go away.

---

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 - Section Access Gotchas

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 Section Access Gotchas. In Qlik, Section Access is the area of script where we connect users to the data to allow row-level security. It is very powerful and relatively easy to implement for many typical use cases.

Before diving in, it is worth pointing out that Qlik Sense Business has a great feature, missing from QlikView when I was writing the Cookbook, that means if a Section Access script would result in the locking out of the developer, then tags the reload as unsuccessful and won't save the faulty setup. This will help with a lot of Section Access issues!

In the recipe I showed a number of different issues. Starting off with this load script:

Section Access;
Access:
LOAD * INLINE [
  ACCESS, USER.EMAIL, Link
  ADMIN, stephen.redmond@notmymail.fake, *
  USER, user1@notmymail.fake, US
  USER, user2@notmymail.fake, UK
  USER, user3@notmymail.fake, Fr
];
Section Application;
Sales:
Load * Inline [
  Link, Country, Sales
  US, USA, 1000
  UK, United Kingdom, 800
  Fr, France, 750
  De, Germany, 940
];

Now, when I login as the Admin, I get to see all the data as expected. However, when I login as user1, I still see all the data when I was expecting to only see US data! What is going on?

Well, a simple rule on the Section Access tables is that all the fields must be in capital letters. Simple fix so:

Section Access;
Access:
LOAD * INLINE [
  ACCESS, USER.EMAIL, LINK
  ADMIN, stephen.redmond@notmymail.fake, *
  USER, user1@notmymail.fake, US
  USER, user2@notmymail.fake, UK
  USER, user3@notmymail.fake, Fr
];
Section Application;
Sales:
Load * Inline [
  LINK, Country, Sales
  US, USA, 1000
  UK, United Kingdom, 800
  Fr, France, 750
  De, Germany, 940
];

Updating the Link to LINK in both tables and now User1 only sees US data. What of user3? If I log in as User3 I get Access Denied! This is because of a second Gotcha - any data field that links users to data and that contains letters, must only contain capital letters! So, "Fr" doesn't work. Simple fix:

Section Access;
Access:
LOAD * INLINE [
  ACCESS, USER.EMAIL, LINK
  ADMIN, stephen.redmond@notmymail.fake, *
  USER, user1@notmymail.fake, US
  USER, user2@notmymail.fake, UK
  USER, user3@notmymail.fake, FR
];
Section Application;
Sales:
Load * Inline [
  LINK, Country, Sales
  US, USA, 1000
  UK, United Kingdom, 800
  FR, France, 750
  DE, Germany, 940
];

Now, User3 can login and see the France data. All is great. Or is it?

But let us just quickly log back in as the Admin - we can only see US, UK and France! Where is Germany? This is a Gotcha whereby all of the users must be linked to all of the data or the security will remove access to the unlinked data. Interestingly, also having an admin user who is linked to none of the data will also work. It does look like the Admin user is linked to everything via the "*", but that isn't actually everything - it is only everything that is linked! Confused yet?

We can fix it like this:

Section Access;
Access:
LOAD * INLINE [
  ACCESS, USER.EMAIL, LINK
  ADMIN, stephen.redmond@notmymail.fake, NONE
  USER, user1@notmymail.fake, US
  USER, user2@notmymail.fake, UK
  USER, user3@notmymail.fake, FR
];
Section Application;
Sales:
Load * Inline [
  LINK, Country, Sales
  US, USA, 1000
  UK, United Kingdom, 800
  FR, France, 750
  DE, Germany, 940
];

Now, the Admin user can see everything. The "NONE" is arbitrary and can be any value as long as it doesn't exist in the data. Actually, I prefer to script things so that at least one user is linked to all of the data values. That way I don't have to rely on the "*".

---

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