Add flexibility to your Qlik Sense app with variables and pick()

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.

Setup

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:

pick(vDashboard,
Sum([Sales Margin Amount])/Sum([Sales Amount]),
(sum([YTD Sales Amount])-sum([LY YTD Sales Amount])*0.2)/sum([LY YTD Sales Amount]),
Sum ([Budget Amount])/Sum ([Actual Amount]))

I then use those measures in the charts.

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.

Conclusion

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.

Qlik Sense On Demand apps

About a year ago, I took a first look at Qlik Sense on demand app generation for a customer. Their problem was not really using big data, but rather that the data the needed for some analysis was simply too big to handle in a Qlik Sense ( or QlikView) app. They had to limit the data to just a shorter time period to make it run at all. Our conclusion at the time was that On Demand App Generation might be the right the right tool, but we had to prioritize other projects and did not complete the project.

Now, about a year later, we have the time to look into it again. And while I think the core concepts and functionality is the same, it has had a facelift, and feels much more like a product ready for production.

Improved user interface

The on demand app generation feature consists of a master or selection app and multiple on-demand apps. The user uses the the selection app, which contains aggregated data, to select a subset of the total data. He then generates an on-demand app, with detailed information about that subset.

The interface for this has been improved a lot. Previously keeping track of your on-demand apps was difficult, now you can find them in the selection app.

All your generated apps are listed in the popup for the selection app, together with information about when they where created and the selections they are based on in the selection app. You can also manage your generated app here, delete it when you are ready with it, reload it with the same selections but new data and open it.

Loading data into the on demand app

In many respects developing on demand apps is just like developing any Qlik Sense app. That’s part of the strength of the feature – you can use your Qlik Sense knowledge for on demand apps too, visualizations are the same, much of the load script will also be the same. What’s new is the connection between the selection app and the on demand app: how do you get the users selections into the ODA, and how should you write the load script.

When we worked with this a year ago, we used Qlik Sense varibles and the script snippets published in Qlik Sense help around using the variables to generate script statements. These snippets helps you generate selection queries in the form of SQL SELECT’s to fetch the data. Our use case is somewhat different: our problem is that we have internally generated data that is simply to big for one app, but the data is not in an SQL database (even if the original source is a database), but in QVD files. This makes it possible to use a much easier method: we can have the selected values inserted into inline tables, and then use them in ordinary LOAD statements.

Using an inline table with the selected values

The first step is to create the inline table:

selected_item_tab:
LOAD * INLINE [
 SELECTED_ITEM
 $(odso_ITEMID){"quote": "", "delimiter": ""} 
];

Item ID’s for all selected and optional items will then be injected into the script. By using prefix ‘odso’ we allow the user to make selections on any level in the ITEM dimension, like product groups etc.

You can then use the inline table when you load your actual data:

LOAD
  ITEM_ID,
  ...
FROM .....qvd (qvd)
WHERE EXISTS(SELECTED_ITEM,ITEM_ID);

This will filter the data so that only selected items are included. You can also combine several dimensions, like time or geography to further reduce the data. No variables or string parsing is needed. So far this seems to work well.

Getting used to the generated apps

Perhaps the most difficult part of the On Demand app generation remains. The generated apps will only select a subset of the data, and in some cases it won’t be obvious what the subset is. Qlik users are not really used to this. Selections will be a two-step process, where the actual analysis will work as we are used too, but the initial selection cannot be immediately changed. This is a new way of working for users. Also totals calculated in the generated app might be useful for reference, it must always be remembered that they are just for a subset. We will try to make the initial selections clearly visible to the user of the generated app, and perhaps we should also try to bring over some summarys etc for the complete data, but still users will have to think differently. That might be the real challenge with this.