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.
Qlikview set analysis is a powerful way for you to create
dynamic data sets mainly for comparison analysis.
The QlikView set analysis is based on the set theory.
A set analysis is calculated before the chart is computed and
redrawn. As you may notice, it is NOT calculated per each row/column in the
chart. So, you may not guess a scope of product depending on the rows that are
products. You may not guess another set of periods depending of the columns if
the Period dimension is in column : the YTD or moving totals you may encounter
are often valid only if the period is not a dimension of the chart. The sets
let you create a different selection than the active one being used into the
chart or table. The created group let you compare the aggregations of this
group and the one of the current selection.
These different aggregations let you compare for example the
sales of the current month and those of the
previous year, create YTD or moving totals. You may also create
market shares, a percentage of the
products, of the year ….
A set modifies the context only during the expression that uses
it. Another expression without any
set will get the default context, standard selection or the
group of the Alternate State.
A set is always written between curly braces {…}. A set can
modify the selection of one or several
dimensions. It can be composed of a single set but can include
also an inner set.
A set analysis is
calculated once per chart (or table). NOT once per row. If you
want a different result in your set analysis according to the
row being calculated and displayed, it will NOT
work
A set may be composed of:
- An identifier
- An operator
- A modifier
These 3 fields are optional. Of course, to write a set, you will
need to incorporate one or several of these
fields.
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.
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.
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
|
Group – group name (Alternate State)
Sum({Book1} [Sales]) : sales of the selection of Book1
Sum({Group1} [Sales]) : sales of the Alternate State Group1 (the
syntax is identical)
Sum({1} [Sales]) : sum of everything (All dimensions are completely
reset to All)
Sum({$} [Sales]) : sum of the current selection (= sum([Sales])
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.
If you use the operators with the equal sign:
: add the set to the current
selection
: remove the set from the
current selection
Sum({1-$} [Sales]) : sum of the sales of the « database » except
the current selection
Sum({GROUP1 * Book1} [Sales]) : sum if the sales for the
intersection of Group1 and Book1 (the
members belonging to both)
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({$} Sales)
This will give the sum of sales for year 2010 and region US. Note
that Year and Region are modifiers in the expression.
Using p() and e()
These functions returns the members that have (or have not)
data. They are based on the associative
model.
Syntax: {) [, Other
dimensions of the Set]>}
Please note that:
- Both functions p() et e() have the same syntax : one p()
returns the possible values, the other e()
returns the excluded values
- They will be inserted into a greater set
- These functions do let you search the Top N products or those
whose sales are greater than X :
they just return the members (ie the products) that have data,
or those that have not.
Using IF statement
It may happen that we need different set analysis according to
the user selection. For example, if the user
chooses one single product, we want to compute/display all of
them. If he chooses several, we want to
show/compute them as they are selected.
The IF statement cannot be used within the set analysis itself.
But we can use a variable … that can
contain a IF statement.
Let’s create a variable vSet that will be a formula:
=if(GetSelectedCount(Product)=1, 'Product=', '$')
And we just need to use this variable in the set itself:
sum({<$(vSet)>} Sales)
