Qlikview set analysis is an ace in the Qlikview’s stack of cards for a developer. Qlikview’s associative data model works well with your current selections. But there are times when you want to create a set or a group of information independent of your current selection. In other words, a data set that has no influence of your current selections. A year over year or a month over month sales comparison is a handy example for you to use Qlikview set analysis for.
Like those fun lego games, you can start with all data in your application and then add, remove or change data to create another set that you want.
I admit that the syntax of the set analysis in qlikview is bit intimidating for a beginner, but it’s not as daunting as it looks once you master the concept.
Let’s start with the basics first.
The very word set analysis ought to remind you of a guy named John Venn. We all have learned Venn diagram in our school days. And, you will revisit this age-old theory again as we learn set analysis in Qlikview.
With that said, set analysis provides a method to create a set based on another set. And, by now it must be obvious to you that it is used in aggregation function.
So, let’s look at the anatomy of a set analysis function in Qlikview, shall we?
It has three major categories: Identifiers, Operators and Modifiers
It starts and ends with a curly bracket: {}
Example:
1. Set analysis begins and ends with { }
2. Modifier begins and ends with < >
3. Operator: =
4. Identifier is not shown but it is $ which is the default state in Qlikview.
For those of you who know SQL, set analysis is akin to a SELECT…. GROUP BY delivered in a concise expression. Armed with this basic knowledge, now you can add many more forms of identifiers, operators and modifiers to create a complex aggregation that otherwise requires many IF statements.
Do you want to master QlikView Set Analysis? Enroll in my Udemy course
For a limited time, I am offering the course just for $29.
https://www.udemy.com/mastering-qlikview-set-analysis/?couponCode=LQSA29
Let’s look at each category and its various options:
1. IDENTIFIERS:
Identifier defines the state of the set.
$ is the default state, meaning the current selection for a given sheet.
1 ignores the current selection and considers all the values in the data set.
Bookmark10 state defined by a stored bookmark. If you are new, please review this article to learn more about book marks.
Now, the fun begins…
You can subtract one state from another. For example,
1 – $ Selects all values from the data set but the current selection.
1- Bookmark10 Selects all values from the data set but those defined by a given bookmark.
MODIFIERS:
Identifier is like a SELECT keyword and Modifier is like a WHERE clause or a filter in your SQL select statement. It works on the selected data set — based on your identifier — to add, remove or to modify existing selections.
Modifiers start with < and end with >. In this example, the returned data set gets modified to filter Year with 2005 and 2006 values. Thus, the resulting data set contains only data for those 2 years.
If you want to do a year vs year analysis, modifier allows you to select data set for the previous year. Like identifier, modifier can make otherwise complicated programming a simple task. For instance, if you want to ignore certain field from your data set, you can simply add a modifier with <Field=>.
OPERATORS:
Operators are like executors of the Qlikview set analysis. Operators somewhat control behavior of modifier statements. They are like a method of an object as they allow interactions between two different data sets in a single expression.
If nowhere else, John Venn’s diagram lives it legacy when you depict how operators work in Qlikview. Remember that we learned union, exclusion, intersection and symmetric difference in our school days when we learned about the Venn diagram.
Example: sum({$ + Year20052006} TotalDue)
Four basic operators:
Set analysis syntax is intimidating for most beginners, but Stefan has created an excellent set analysis wizard that makes this daunting task easy to deal with.
As you can see, Qlikview set analysis is a powerful way for you to create dynamic data sets mainly for comparison analysis. We have just made our feet wet in the ocean of possibilities with this introduction.
In the next Qlikview tutorial, we will try to swim together with some advance techniques using dollar sign expansion. Till then, so long…
Do you want to master QlikView Set Analysis? I have an online course with over 60 lectures to teach you from basics of Set Analysis to advanced topics.
Set Analysis is one of the most powerful features and my goal is to help you master Set Analysis so that you can instantly start using it in your next QlikView or Qlik Sense application.
For a limited time, I am offering the course just for $29.
https://www.udemy.com/mastering-qlikview-set-analysis/?couponCode=LQSA29
photo by: knitvana
