Set Analysis in QlikView and Its Components
Set analysis is an advance expression in QlikView. It allows you to create data sets independent of your current selections. It’s mainly used in the comparative analysis. QlikView Set Analysis allows you to perform flexible aggregation. Usually aggregation occurs on the set of values in the current selection. But many a times business requirements demand to perform aggregation on a Set of records that are independent of current selection.
For instance in the below screenshot the values in the table box shows based on the front end selection of the year = 2001. This selection will reset if you clear the screen. But if client need on the loading of the dashboard the data in the table box to show with respect to year=2001, then in this situation we need set analysis expression. It will make our statistics with out selection any filter on the screen .
It has three major categories: Identifiers, Operators and Modifiers
Set Analysis expression, starts and ends with a curly bracket: {}
Example:
- Set analysis begins and ends with{ }
- Modifier begins and ends with < >
- Operator: =
- Identifier is not shown but it is $ which is the default state in Qlikview.
Set analysis is a kin 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.
Let’s look at each category and its various options:
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.