by erikwett·Comments Off on Qlik Sense pick() performance: a follow-up
A few weeks ago I wrote a post about a pattern I have used to make Qlik Sense apps more flexible and allow the user to choose what visualizations to show. Feedback I got on this(from Rob Wunderlich) is that there is a performance problem with pick(), that Qlik Sense does not optimize pick calls quite as well as you might think, that it might evaluate function calls where the values are not actually used.
I vaguely remember this from Master Summit for Qlik where I believe it was mentioned. At the same time I do know that Qlik from time to time makes improvements in the optimization, so it might not be true anymore. Anyhow, this is something you could easily verfify. And I’ve got a tool to make at least a first assessment of this, the Developer Tools extension. I originally made this to help finding id’s for visualizations, but later has added some timing features to it, that can give us a rough idea about performance in your visualizations.
To do this I made a copy of the example used in my previous post. You find it here. In it I made a new sheet and copied one of the visualizations using pick() into it. I chose the pie chart (not because I like pie charts, but because I wanted one with some data in it). I then made a copy of it, and in the copy removed all pick() calls, together with alternatives 2 and 3 in that call, leaving only the first one, which is the expression that will actually be used of the three in pick(). And I add the DevTool extension to the same sheet.
Run the test
When all is ready, I switch into analyze mode and click on the DevTool button, which creates popups for the two charts. I then make some selections, use back and forward, and I get the following result:
The left chart is with pick(), the right one without. The most interesting figure is the one at the bottom right, the maximum calculation time. As you can see there is a significant difference: 147 ms compared to 57 ms. Seems like Rob is wright: there is (still) a performance problem with pick(), even though in my test case calculation time is so small that users will probably not notice.
This is a very rough test. It’s main advantage is that you can do it quickly. Since there is such a clear difference between the two alternative the result that pick() is considerably slower can be trusted. And the fact that the no-pick alternative actually takes some time (57 and not 5 msek) means that there is some calculation going on, just taking the result from the cache would be faster.
One of the main uses of the Variable extension for Qlik Sense is to allow users to quickly with a click on a button switch dimensions or measures in a chart. This has been described in Qlik Community here and here.
But perhaps you want to change more on the click of a button. You need to change not only a dimension or a measure, but several. And a title, or a subtitle or whatever. In that case you can use the Qlik pick function:
pick(n, expr1[ , expr2,...exprN])
n is an integer between 1 and N.
pick( N, 'A','B',4, 6 )
returns 'B' if N = 2
returns 4 if N = 3
Variable and Pick()
The combination of Pick() and a variable can be very powerful. It also has another advantage. Sometimes the expressions you need to switch between are fairly complicated. Setting them up in the property panel of the variable extension can be difficult, and it’s not really the right place for Qlik expressions. Using the pick function helps you keep your expressions out of the property panel and instead where they belong, in the chart setup or in a measure or dimension definition.
To illustrate this I’ve made a small example, available in the Github repository here. To try it you of course need to install the actual extension. If you don’t have that already, you’ll find it here. I have called it Switch dashboard, because what it does is that it in the same area of the sheets allows the user to switch between three different setups, Margin, Sales and Budget (the actual data and the charts are taken from the Consumer Sales demo).
Initially when you open the app it looks like this:
Click on the Sales button and it will change to:
Just about everything has changed: another KPI is displayed, dimension in the middle (donut!) chart, measure in both charts, titles. And if you click the Budget button, you will see a third setup, I’ll leave that to you to verify.
To set this up you use classic Qlik features, like script statements and expressions, using the pick function. But you could also use Qlik Sense dimensions and measures. Lets see how its done:
Step 1: define the variable and set default value in the script.
First we define the variable we will use. Using standard Qlik naming conventions I’ve called it vDashboard and added it to the script:
SET vDashboard = 1;
Remember that pick numbering starts with 1, so do not use zero. The plan is that 1 should be our first dashboard, Margin, 2 should be Sales and 3 should be Budget.
Step 2: set up a chart dimension
Now we use the variable, together with the pick function to define dimensions. In the donut chart I’ve defined the dimension like this:
=pick(vDashboard, [Product Group Desc], [state_name], [Sales Rep Name])
This will mean that we use different dimensions depending on the value of the vDashboard variable.
Step 3: Define measures
But we want to make the measures switchable too. But since we will use the same measure in several places, I have chosen another method for measures. I have defined two master measures, called (not too much creativity here, I’m afraid) ‘Dashboard KPI 1’ and ‘Dashboard KPI 2’. Definition of those looks like this:
Expressions in Qlik can be a lot more complicated, so it’s an advantage to be able to use the expression editor and get the syntax correct for this. Also I have been consistent in the formatting, so that each pick alternative is on its own line.
Step 4: Labels etc
Finally we want to make sure that chart titles reflect the content. Luckily we can use pick() in the title definition too:
=pick(vDashboard,'Margin Amount Over Time','Sales Over Time','Budget Amount Over Time')
We can use this approach everywhere in the property panel wher an expression is allowed. (A note for you extension developer: make sure that as much as possible in your extension can be set up with expressions, that will increase the flexibility of your solution a lot).
Step 5 (and final): Set up the extension
Finally we need to make it possible for the user to actually change the value of the vDashboard variable, thereby switching the dashboard contents. We do this by adding the Variable extension to the sheet and setting it up like this:
As you can see this is really straightforward. We use the values 1, 2, 3 etc and labels showing what we display for the three alternative versions.
A more advanced setup is using the new dynamic values option, and have a Qlik expression returning the alternatives. That way you could have a different set of dashboards for different users. Only remember that the first alternative needs to be available for all users, since it will be the default.
The combination of the pick function and a variable (and the variable extension) makes really powerful solutions possible. And note that this is classic Qlik skills that are needed: Qlik expressions and the well-known pick function. You do not need to start with web development for this.