Set Analysis in Qlikview : Example and Syntax
What is Set Analysis
QlikView has feature called SET ANALYSIS that provides us a way to add this context. Set analysis predefines the SET OF DATA that our charts / tables use. So, using a Set Expression, we can tell our object (chart / table) to display values corresponding to various sets of data (e.g. a pre-defined time-period, geographic region, product lines etc.).
It Can can be understood by a simple analogy of how Qlikview works. We make selections on certain variables and the changes reflect in the entire application. This happens because through our selection, we have created a set of data which we want to use. In a similar fashion, using Set Analysis feature, we can pre-define the data to be displayed in our charts.
Syntax & Examples
Here, details for creating a correct Set Expression. Also, here is some useful examples for you to get your Hands On right away.
Before start
Some features and characteristics for Set analysis are:
- It is used to create different selection compared to the current application selections
- Must be used in aggregation function (Sum, Count….).
- Expression always begins and ends with curly brackets { }
Follow these steps to build up your set expression and use the correct syntax:
1. First of all, you need to define what you want your expression to return. A good method to get it right would be to answer the following questions first:
o What field will I use in my expression? (For example: ‘Sales’, containing the sales amount for every invoice.)
o How will I aggregate the field? (It can be using Sum, Count, Avg, etc.)
o What explicit selections do I need in my expression? Here you define a Field and its value(s). (For example: I want ONLY the ‘South’ Region. Another example would be to only include values associated with certain Year.)
o Do I need to exclude/ignore some selections or values?
2. After you’ve answered the questions, you can go on to compse the expression. If you want to Sum the Sales amount, you would start with something like:
o Sum(Sales)
3. Then, whe need to add the Set portion of the expression.:
o This portion goes just after the first parenthesis, before the Field Name. The Set Expression will be enclosed in curly brackets: {set expression}.
o After the first curly bracket we add either a dollar sign (which means the record set will be based on the current selections) or a number 1 (meaning we will use the full record set of all the records in the application). We will use the dollar sign to illustrate, since it is the most common, so you will now have {$}. Important to note is that the dollar sign can be ommited and the set expression will not be affected. It is good practice, however, to use it.
o After the Dollar Sign, we define the fields that will play in our set expressions. All of these field-value definitions will be encolsed in less-than and grater-than symbols (< >). The syntax is FieldName = {FieldValue}. If FieldValue is a literal or text, you should enclose it in single quotes. If you want to use a search string as the FieldValue, enclose it in double quotes. Here are some examples:
- {$
} will result in a record set taking the current selections ($) where the Field Region has a value of ‘South’.
- {$
} will give return a record set based on current selections ($) where the Year is 2010 EVEN if you select something else in the field Year.
- {$
} will give you the record set based on the current selections where the Year matches the search string “20*”, meaning all years that begin with “20″.
- {$
=2007″}>} Will return a record set where the Year is greater than or equal to 2007. Notice that here we are using a search string.
- {$
} will give you a record set based on the current selections where Region is equal to South or North and Year is equal to 2010.
4. Your final expression should look similar to:
- Sum({$
} Sales)
5. You can use variables instead of the hardcoded Field Value:
- If your variable is a number, use it as Field = {$(MyVariable)}
- If your variable is text, use it as Field = {‘$(MyTextVariable)’}
- If your variable should be used as a search string, use Field = {“$(MySearchVariable)”}
6. Also, you can create calculations to use them as Field Values:
- Year = {$(=Max(Year))} It is just as if you would use a variable (described above), just with an equal sign. NEVER forget the equal sign here.
For info about Set analysis in Qlikvew, Download below pdf.
Download Set Analysis in Qlikview.pdf
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 second
* 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.
Examples:-
Set_Analysis_Operator_Qlikview_example
Dollar Sign Expansion:} Sale) “
If we want to compare current year sale with previous year, previous year sales should reflect values in relation to current selection of year. For example if current selection of year is 2012, previous year should be 2011 and for current selection of year 2013, previous year is 2012.
“=Sum ({$
Above expression always returns sale for previous year. Here $ sign (Font color red) is used to evaluate the value for previous year. $ sign is used to evaluate expression and to use variables in set modifiers. If we have variable that holds last year value (vLASTYEAR) then expression can be written as:
“=Sum ({$vLASTYEAR)} >} Sale) “
Also
Indirect SET ANALYSIS: Function P() and E() } Sale)}Company_Name)>}Sale)
Let us take a scenario, where we want to show current sales of the companies who had sales last year.
Expression should be similar like:
=sum({$
First we have to identify companies who had sales last year. To fix this problem, we will use function P() that is used to identify values within a field and function E() that exclude values within a field.
Set_Analysis_Indirect_Qlikview
Finally, we have expression:
=sum({