Set analysis with variables – dynamic secret sauces and meet your new best friend in Qlik Sense

Published on March 3, 2022 by Kevin

I discovered set analysis with variables recently and thought it would be useful to share how I used them. Even if you know how to use them, check out the end of this blog where I use an extension to make variable changes more usable – your new best friend! This guide is applicable to Qlik Sense in general and is not Kognitio specific.

What is set analysis?

In Qlik Sense you can perform set analysis to specify the window of data you want to look at. For example between a set of dates. But you’re probably thinking, “I can just select it from any of the visuals on my dashboard and it’s a lot more intuitive that way.”

You’re right, but depending on the visual, selecting them isn’t always easy and with variables things get even better as you can dynamically change them. The smart search in Qlik Sense also only searches for values in fields, which makes it a bit limited. Another reason is that you may wish to limit the data in one chart for comparisons with all of the data in another.

Basic usage

I’ll be using a date window for my example.
Plot a simple bar chart, I’m going to use total sales across departments.

 

For this, my measure expression is very simple:

=Sum([TOTAL_SALES])

The set analysis syntax comes before the field that you’re performing the aggregation on.
For example if you want to only look at sales data for a specific date:

=Sum({$<SALEDATE={'01/01/2015'}>} [TOTAL_SALES])

This sets the date as a criteria for your set, so the sum of [TOTAL_SALES] only sums the values satisfying that. But you can put functions in there too:

=Sum({$<Year(SALEDATE)={‘2015’}>} [TOTAL_SALES])

This uses the year function to extract year from our sale date so that we can compare it with our desired year. You can even add multiple values for this:

=Sum({$<Year(SALEDATE)={‘2015,2016’}>} [TOTAL_SALES])

You can even add a range if you don’t want to manually insert the numbers but you’ll need a double quotes for this instead:

=Sum({$<Year(SALEDATE)={“>=2015<=2017”}>} [TOTAL_SALES])

With variables

What if you want something more dynamic for your values? Then you can use variables.

To open the variables menu; when in edit mode, in the lower left you will see this icon:

 

Clicking on it brings up a list of variables and you can create new ones with the “create” button. Continuing with our date example, we’re going to make two for our date range:

 

Note: it’s advised to name your variables with a lowercase ‘v’ at the beginning to make it easy recognisable.

We’re going to call it ‘vStartDate’, give it the value ‘2015’ and ‘vEndDate’ with value ‘2017’:

 

Close the window and we can use it in our set analysis. Back to our measure expression, insert it where we had the hard set value before:

=Sum({$<Year(SALEDATE)={"<=$(vEndDate)>=$(vStartDate)"}>} [TOTAL_SALES])

The main benefit of this is that you can use this set analysis expression in all your dashboard measures and when you change the variable value, it will apply to all of them.

Changing variable values

Of course you’d have to go into the variables menu to change them which isn’t very intuitive from a user perspective and something I’d like to see change. However I discovered an extension which allows you to create a button to change variable values called Sheet navigation + actions

So you can set buttons to change variable values; in this image I made buttons to change the variable that the set analysis is based on. A single button can also do two actions which is perfect for our date range. For our date example we can can set something like:

 

To change the date values to 2014 and 2016 instead. Do note that when you enter the variable to change, and its value, Qlik Sense will add a “=” which you will need to remove.

You can even set a variable for the measure we want to use:

=Sum({$<Year(SALEDATE)={"<=$(vEndDate)>=$(vStartDate)"}>} $(vVarOne))

And create buttons to change it. I find this to be much better than selecting alternate dimensions/measures because it offers a one-click interface for the user. This makes it much simpler to use especially when you have a lot of alternate measures to choose from.

Another useful tip is to use this in chart titles so that the headings change dynamically depending on the variable used for the measure.

=Total sales between ' & '$(vStartDate)' & ' and ' & '$(vEndDate)'

Want to speed up your web site?

WP Bolt makes it easy and affordable to host your sites using popular Cloud providers.

View Pricing & Sign Up Now