Task Tips for Set Expressions and Analysis with Qlik
The associative nature of Qlik provides an excellent canvas for users to do data analysis and discovery. Normally, when a user makes a selection, aggregation functions, such as Sum, Max, Min, Avg, and Count aggregate over the selections that the user has made: the current selections.
A user’s selections automatically define the data set to aggregate over. With set analysis you can define a group that is independent of the current selections.
A few examples of the usefulness of set analysis include:
- If you want to show a particular value, for example, the market share of a product across all regions, irrespective of the current selections.
- You want to have a Prior Year Column calculated based on the current selections. For example, A user is analysis Sales and filters to the year 2021, but you want to provide a column with Total Sales in 2021 and a column with Total Sales in 2020. Set analysis can be used to override the year selected to be year selected minus one in the calculation.
- Set analysis is also powerful when making different sorts of comparisons, such as what are the best-selling products compared with poorly-selling products.
There are many areas that provide assistance with set analysis. Set analysis works the same across QlikView, Qlik Sense on Windows and Qlik Sense SaaS. Here is a link to the Qlik help files for Qlik Sense on Windows, but it is the same for all versions: Set analysis and set expressions ‒ Qlik Sense on Windows
What follows is a deeper dive into Set Analysis to show how to leverage it in a more advanced manner.
There are many ways to accomplish the different tasks used in set analysis. I’ve collected some of my favorites here, which can be easily copied-and-pasted into your application. Just enter your own variables to use in your expressions!
In some cases, you will want to ignore all selections in an entire table so you can manually list each field in your expression. Your default expression will look like this:
You can streamline this process if you want to enter a large number of fields. For example, at Solve we use a standard calendar that has 30 fields. It would be extremely error-prone and tedious to manually code this, and the process would obscure what you’re actually trying to accomplish with the expression.
To save time, you can use an expression to override the system data model and automate the field selection building process. This expression uses the concat function to string all the field names together, creating something that looks like this:
You can use dollar sign expansion in your set analysis to insert this into your expression:
Great! You’ve now overridden the selections for the entire table. However, other developers will probably have no clue what you just did. To help, you can use some simple variable reference expressions to put into a variable and override the selections on the calendar table.
Overriding Selections with Parameters
What if you want to override selections on any table, not just the calendar section? If you’re anything like me – who has 300 tables in my data model – you don’t want to risk screwing up all those variables with changes made by this expression. So to save time again, you can add parameters to these variables to make them a little more dynamic. This new expression will allow you to override selections on whatever table you want:
You’ll notice the “$1” in the expression – this part is a parameter that lets me pass the name of a table, which lets me override those selections on any table I want. You’ll also notice I removed the equal sign from the beginning of the expression (more on this later).
Calling the Variable
To call the variable, you’ll need a two-step process that I’m calling “double dollar sign expansion”.
You need the “double dollar sign expansion” to insert the parameter into a string, which you use to evaluate in separate steps. Otherwise, the expression doesn’t parse. This took me a while to figure out but WOW is it cool!
Overriding All Selections but One
Lastly, you may want to ignore all selections on your table except one. Normally you would call our magical variable for ALL tables, which would be easy – if you didn’t have so many tables that is. For data models with a large amount of tables, I’ve managed to find a solution deep in the QlikView Help Pages for Alternate States.
When writing expressions with Alternate States, you can make some fields obey the default state (or any alternate you specify) by naming the state and field in the operand of the set modifiers. This gives you an expression that looks like this:
In my calendar example, I’m setting my “OrderDate Year” to obey the selections of the state, and then the “OrderNumber” to the alternate state named “CompareState”. (I won’t be talking about how to create alternate states in this post, but I did want to call attention to it as it’s a set analysis expression I don’t think is widely used.)
If you want to make your expression obey only one table, you can apply the same concepts explained above using alternate state syntax.
For example, this is the variable I’m calling “saObeyTable”:
And this is the expression where I call the variable:
Adding the set identifier “1” makes the entire set analysis expression ignore all selections, and then calling the variable strings together all fields from the “Orders” table. Done!
The above are tasks that many developers have to repeat over and over again – and one’s that would benefit a lot from some additional automation. Hopefully you can add these task tips to your toolkit and save some time and effort going forward!