My last post concentrated on users starting off with Set Analysis. As a recap, set analysis allows users to create a different selection than the active Qlik state by defining an alternative set of records within the set expression.
It’s a very powerful functionality for comparison on topics such as Current vs. Previous year or Products purchased vs. not purchased.
In the last blog we looked at the basic setup
- Start your aggregation – Sum(Sales)
- Add your first set of brackets – Sum( {} Sales)
- Add your second set of brackets – Sum( {<>} Sales)
- Add the field to limit on – Sum({<Year = >} Sales)
- Add the data to limit on – Sum({<Year = {‘2019’}>}Sales)
Although this is correct, the year number it is a static figure and would need to be changed every year. To make this dynamic, we need to start looking at more complex set analysis.
Dollar sign expansion & variables
To make the year in the function above dynamic, variables can be used to hold a value or calculate a value to be passed to the set analysis. To do this, first of all go to the variable option at the bottom left when in the edit pane.
If we create a new variable called vMaxYear and in the expression use =Max(Year) or any other field you want to use.
It is always best practice to name your variables with a convention. Some people use var_NAME, others like myself prefix a lowercase v at the start to signify it is a variable.
This variable will then calculate the maximum available year in your dataset. We can now use this variable in our set analysis
To use variables we need to use something called Dollar sign expansion. Dollar sign expansion evaluates the content of the variable and places the output value into set analysis. This is not necessary when the variable is a static number or text because there is nothing to evaluate.
To add the dollar expansion, we can follow the below steps
- Start with the basic set analysis minus the hard coded year – Sum({<Year = { }>} Sales)
- Add a set of double quotes – Sum({<Year = { “”}>} Sales)
- Add the dollar and a set of normal brackets – Sum({<Year = {“$()” }>}Sales)
- Add the variable – Sum({<Year = {“$(vMaxYear)” }>}Sales)
The dollar tells Qlik to look inside the variable and bring back the result. This is now saying Sum of Sales where the year is the result of the variable.
We can make variables for this year and last year to create variance calculations. We can also use variables to select between periods of time
Sum ({<Year= {“ >$(=date(vMinYear)) <= $(=date(vMaxYear))”}>} Sales) – Returns Sales for time period between vMinYear and vMaxYear
Using a Function
You can add functions/aggregations within set analysis to allow for a dynamic change of expressions based on selections made by user. This is much like the variable example. Calculations enclosed within set analysis are evaluated with the use of dollar sign expansion and an equal sign.
Examples of this are:
Sum ({< Year = {“$(=min({1}Year))”} >} Sales) – Returns Sales for the first year in Year field
Sum ({<Year = {“$(=max({1}Year)-1)”} >} Sales) – Returns Sales of the maximum Year minus 1 in the Year field
Sum ({<Customer = {“= Rank(sum(Sales), 4) <=10”} >} Sales) – Returns Sales of Top 10 Customers
Using a Boundary
Just like the above variable example, boundaries allow users to use greater than and less than within set analysis and returns a record set within the boundary specified in set analysis
Examples of this are:
Sum ({<Hour = {‘<12’}>}) Sales) – Returns Sales that occurred before 12 o’clock
Sum ({<Hour= {“>=13 <=17”}>}) Sales) – Returns Sales that occurred between and including 13:00 and 17:00
Advanced Searching
You can also use set analysis in conjunction with wildcards and functions to search for records which contain a string or value specified. It minimises the need to list all values separately.
Sum ({<Product = {‘*AB*’, ‘*AT*’}>} Sales) – Returns Sales for current selection of products with the string ‘AB’ or ‘AT’ in the Product name
I hope that this explains the more advanced set analysis functions and allows people to build much more advanced dashboards.
