Introduction to QlikView Set Analysis
In any dashboard/ report, it is always best to have more contexts, which makes our dashboard more meaningful. Adding context to our dashboard basically means that we have to provide a lot of insights, comparison with the relevant products and timeline. This will make our dashboard action-oriented. This can be achieved with the help of the Qlikview feature called the Set Analysis which helps in adding context to our dashboard. By using the set expression we can make our objects like chart/table to tell us information related to different sets of data. Let us try to understand what exactly is set analysis, this feature in qlikview is used to segregate our data that are available in different sheet objects into multiple sets and keeps some of their value unchanged, in this way we filter the data in one sheet which helps us to understand the corresponding changes in the different sheets, in this way we will be able to compare our set of records in the same table/chart by using set analysis.
Syntax and Examples of QlikView Set Analysis
Before we dig into the syntax of the set expression let us understand the major features of set analysis,
- It should be used in aggregation functions
- Set expression starts and ends with curly brackets {}
- It is used for the creation of different selection
- All the modifiers start and end with <>
- An operator that should be used in the set analysis is =
- An identifier used is $
We use set expressions to perform our set analysis and these expressions are written within {}. It can contain the below-mentioned elements
- Identifier
- Operator
- Modifier
Ideally, the logical sequence of set expression would start with an identifier and then comes the modifier and finally the operator.
1. Set Identifier
Identifiers are the basic elements of a set expression is used for the identification of active set, it is the state of the data set and the set expression starts with the identifier as its first element. To make our understanding much easier we can think of identifier as the FROM statement we use in SQL.
Syntax:
sum ({identifier} document name)
Identifiers can be any one of the below-mentioned options
- $: Dollar symbol talks about the currently selected set. E.g. sum ({$} tax) – it gives the sum of tax document for the selected set.
- 1: Symbol 1 indicates that it does not bother about the selected set and it will consider the entire data. E.g. sum({1} tax) – it gives the sum of all the data that are available in the tax document irrespective of the current selection
- Bookmark: This will consider any bookmarked set for the procession. E.g. sum ({2010 tax} tax)- it gives output as sum of the tax data that are stored in the bookmark named as 2010 tax.
- $1: This symbol represents the set that is chosen as the previously selected set, we can also use $0 for current and $-1 for the forward selected set. e.g. sum({$1} tax) – it gives output as the sum of tax data that are selected previously.
- Myaltstate: this used to refer to a selection that is made in an alternate state.
2. Set Operators
Operators perform the function by combining and manipulating our selected set. The operand for the operator will be the selected set and the result will also be a set. The thumb rule for the evaluation of operators is from left to right. Qlikview uses these 4 as the basic operators
- +/Union: This operator stands for union and it will just arbitrarily combine the two set’s data and give as output. E.g. sum({ $ + 2010 tax} tax) – this expression gives output as a set which is a combination of the selected tax data set and the 2010 tax data set.
- -/exclusion: This is an exclusion operator, where it will give an output set that excludes the mentioned second operand. E.g. sum ({1 -$} tax) – this expression gives the output as a set which excludes the currently selected set that is mentioned in the expression by dollar symbol.
- */intersection: This returns a set which is an intersection of both the sets mentioned as an operand. E.g. sum ({$ * 2010 tax} tax) – this expression gives the output as the common data set between the currently selected set and the bookmarked set.
- / – this is an XOR operator; basically it gives the symmetric difference between the two sets. E.g. sum ({$ / 2010 tax} tax) – this expression will return the result as a set where the sum of the tax data is calculated for the contained and not the shared data along with the bookmarked data.
The precedence of the evaluation of operators in Qlikview is that intersection and XOR operators are evaluated first and then union and exclusion operators are evaluated.
4.5 (2,345 ratings)
View Course
3. Set Modifiers
Modifiers are used to make the necessary and specific alterations to the set of data that are selected, we can imagine modifier as a WHERE statement that we use in SQL. It uses many field names where each field name will have its own selection. Modifiers are always written with the <> brackets inside the set analysis expression. Modifiers by default will consider the identifier as the currently selected set if we do not mention a separate identifier. Let us try to understand it through an example:
- Sum ({$ < year= {2014} >} tax): This gives the output as the sum of selected tax data for the year 2014.
- Sum ({$ < year= year – {2014} >} tax): This expression returns the output as the sum of the selected tax data excluding the year 2014.
- sum ( {$ < product = {“ ”} >} tax): This will give the output set as the sum of all the tax data for the selected set for all products.
- Sum ( {1 <Year = {2003, 2004}, Country = {‘India’}> } tax): This expression will give the output as sum of all the tax data for the year 2003, 2004 where the country is India.
Conclusion
Qlikview’s set analysis is a very powerful function as it helps us in doing all kinds of filtering and manipulation of data with the same chart or table. The syntax for the set expression can be more complex as well based on what kind of data we need to filter and take. There are a variety of applications for set analysis in the market, for example, we can use them for the comparison of our most sold products vs. least sold products, hence learning this would really help in understanding the data well.
Recommended Articles
This is a guide to QlikView Set Analysis. Here we discuss syntax and examples of qlikview set analysis which includes set identifier, operator and, modifier. You may also look at the following articles to learn more –
