QlikView Set Analysis: Linchpins
Business Analytics Specialist
John Daniel Associates, Inc.
Development in QlikView can prove to be simple for most seasoned Business Intelligence professionals. Once you’ve developed in one platform, you can translate those skills to other platforms and be successful. There are a few changes in syntax between platforms, but the basic ideas are the same.
One big exception to this in QlikView is Set Analysis. Oftentimes we hear Business Intelligence professionals confess that Set Analysis is a totally different monster all together, and that they’ve had trouble wrapping their head around it. So in this post, I would like to go over commonly used Set Analysis syntax.
The most basic Set Analysis Expression would look something like this:
Let’s build on this syntax. Currently, we are taking a sum of the Amount, when Year = 2015.
Ignore Specific Fields
What if we want to ignore certain Field selections that a user may make? We would add in the following command:
We added “AccountID =” to the syntax. This tells the expression to ignore any selection made by the users in the AccountID field.
Exclude Specific Field Values
Let’s assume we want to remove a certain selection value from the expression. There are two questions you must ask:
- Do we want to ONLY remove a certain selection, but allow the rest to be sensitive to other user selections? OR
- Do we want ALL values in the field, except the one we are removing?
Each scenario requires different Syntax.
Solution to Question 1:
In this example we are excluding any data where City = Pittsburgh. The “-=” designates “does not equal.” You’ll also notice that QlikView is throwing back the “something is wrong” red line, but in the upper left corner it states “Expression OK.” The “Expression OK” is the indicator you want to pay attention to first. If it states everything is OK, then we can ignore the red lines.
This syntax will allow users to still make other selections within the City field, but all results will exclude Pittsburgh regardless if it’s selected. If they select Pittsburgh, the results will come back as ZERO.
Solution to Question 2:
In this example we are still excluding Pittsburgh from the Expression results, but also forcing the expression to consider all other Cities within the results regardless of user selection. If a user tries to limit the selection to just Cleveland, the results will still show for all cities, excluding Pittsburgh.
Either syntax will work – it all depends on the business requirements for this expression.
Ignore ALL Selections, Except a Few
QlikView allows you to consider a full data set regardless of a user’s selections. You will need to create an expression with Set Analysis that is static regardless of selections.
To accomplish this we add “1” to our expression.
The “1” added before the “<” tells QlikView to consider the full data set and only apply the filters defined within the Set Analysis.
There are times when we will want SOME user selections to be considered. We can accomplish that with the following:
The Addition of “AccountID = $::AccountID” now allows for the AccountID field to be sensitive to user selections. The “$” designates the Default State. If we wanted to make the selections come from an Alternate State instead, we’d just change the “$” to the Alternate State name. See below.
These are some of the more basic and most commonly used Set Analysis syntax. In a future blog post, I will discuss some advanced Set Analysis techniques to help define date ranges and conduct in depth comparison analysis.