Most of the QlikView Consultants think that Set Analysis is a complex feature to use in QlikView. This blog intends to change this notion and make Set Analysis look something easy to use rather than complex.
Back in our school days we learned set theory in mathematics We used to draw Venn diagrams that showed the sets as circles and displayed the commonality as an intersection. The QlikView set analysis is based on the set theory.
Why use set analysis?
In QlikView the selection you make can be considered as a set, the selection not made are another set. Using set analysis these sets can be customized to make a conceptual selection so that we display the desired values on the charts. Set analysis always involves an aggregation function.
Simple example that uses set analysis to display the Sum of Sales for a Year
1)Determine the field that needs to be aggregated.
Sales in our case.
2)Determine the type of aggregation that is required.
Sum in our case.
expression - Sum(Sales)
3)Lets change the expression to set analysis
Add Curly brackets
expression - Sum({} Sales)
4)Add a identifier $ ($ represents the records in the current selection)
expression - Sum({$} Sales)
5)Add Angle brackets
expression - Sum({$<>} Sales)
6)Add a Modifier in between the angle brackets
expression - Sum({$<Year={2008}>} Sales)
The simple set analysis expression is now ready. The expression will give us a sum of sales for the year 2008 with respect to the current selection.
Basic set analysis syntax consists of Identifiers, Operators and Modifiers.
Identifiers - Identifiers can be compared to a from statement in SQL. The identifier determines the set we will be operating on. Identifier begins and ends with the curly brackets. Following are the identifiers we can have in set analysis.
Identifier | Description |
0 | Represents an empty set |
1 | Represents the full set of all the records in the application |
$ | Represents the records of the current selection |
$1 | Represents the previous selection |
$_1 | Represents the next (forward) selection |
Bookrmark01 | Represents the bookmark ID or the bookmark name |
example - sum({1) Sales) will return total sales in the dashboard irrespective of the selection.
Operators - Operators are used to operate on the identifier. Following are the operators we can have in set analysis.
Operator | Operator Name | Description |
+ | Union | Returns a set of records that belongs to union of sets. |
- | Exclusion | Returns records that belong to the first but not the other of the two set identifiers. |
* | Intersection | Returns records that belong to both of the set identifiers. |
/ | Symmetric Difference | Returns a set that belongs to either, but not both of the set identifiers. |
Example : sum({$ * Bookmark01} Sales) returns the sum of sales that is an intersection between the bookmark and current selection.
Modifiers - Modifiers can be compared to the Where clause in SQL. These are additional or changed selections. The modifiers should always be in angle brackets.
Example - sum({$<Year={2010}, Region={“US”}>} Sales)
This will give the sum of sales for year 2010 and region US. Note that Year and Region are modifiers in the expression.
The Set Analysis Wizard created by Stefan Walther could be found at link Set Analysis Wizard for QlikView | qlikblog.at
Hope this basic post on set analysis is helpful.
Comments