Blogs

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

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

Experiment for yourself

Hopefully this was a useful introduction to set analysis with and without variables. Try different variable types and see how you can enhance the user experience. If you find something else that I’ve missed then let me know in the comments.

For more, check out this video to see Qlik ODAG in action with some retail data.

Interested in how Kognitio works for BI in general? Check out this page for BI and analytics leaders. What about data science?  Here’s more about that too.

Leave a Reply

Your email address will not be published nor used for any other purpose. Required fields are marked *


The Worst Bus Stop in London

We analyzed 4,938,534,706 data points, 19,687 individual bus stops, 675 bus routes and 9,641 buses. A snapshot of three months of bus journeys to find London’s worst bus stop. That’s a lot of data.

See the results