Tag: qlik script

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.