A deep dive into set analysis with Qlik

Set analysis is a powerful tool when making different sorts of data comparisons, such as what are the best-selling products compared with poorly-selling ones.

Here is a deep dive into advanced uses of set analysis, which works across the same across QlikView, Qlik Sense on Windows and Qlik Sense SaaS.  

What follows is a deeper dive into Set Analysis to show how to leverage it in a more advanced manner.

Overriding selections

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:

Sum({<OrderDate =, [OrderDate Day Name] =, [OrderDate Month] = ,…>}OrderAmount)

You can streamline this process if you want to enter a large number of fields. For example, if you 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: 

=Concat({1<$Table = {'OrderDate Calendar'}>} '[' & $Field & '] = ',',')

You can use dollar sign expansion in your set analysis to insert this into your expression: 

Sum({<$(=Concat({1<$Table = {'OrderDate Calendar’}>} '[' & $Field & '] = ',','))>} OrderAmount)

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. 

Sum({<$(saOverrideCalendar)>}OrderAmount)

Overriding selections with parameters

What if you want to override selections on any table, not just the calendar section? And you don’t want to risk messing up all those variables with changes made by this expression. 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: 

Concat({1<$Table = {$1}>} '[' & $Field & '] = ',',')

You’ll notice the “$1” in the expression – this part is a parameter that lets you pass the name of a table, which lets you override those selections on any table you want.

Calling the variable

To call the variable, you’ll need a two-step process that I’m calling “double dollar sign expansion.”

Sum({<$(=$(saOverrideTable('OrderDate Calendar')))>}OrderAmount) 

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. 

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, the QlikView Help Pages for Alternate States provides a solution. 

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: 

Sum({<[OrderDate Year] = $::[OrderDate Year],OrderNumber = CompareState::OrderNumber >}OrderAmount)

In my calendar example, we set my “OrderDate Year” to obey the selections of the state, and then the “OrderNumber” to the alternate state named “CompareState”.

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 a variable called “saObeyTable”:  

concat({1<$Table = {$1}>}'[' & $Field & '] = $::[' & $Field & ']',', ')

And this is the expression where you call the variable: 

Sum({1<$(=$(saObeyTable('Orders')))>} OrderAmount)

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.  

How Wipfli can help

Business intelligence is our passion. The team at Wipfli thrives on helping you grow your business through solving critical problems by using data. Learn more on our data and analytics web services page.