Why use the sets
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 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 thesefields.
Limitation:take care, 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 NOTwork. Change the way to compute your data (model, if statement) but forget the set analysis.
The identifier
0 – empty set
1 – complete application
$ - current selection (take care that sometimes your chart or table does not use the current selection but an
Alternate States)
$1 – previous selection ($2: the second previous selection, etc.)
$_1 – next selection
Bookmark01 – name or ID of a bookmark
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 completly reset to All)
Sum({$} [Sales]) : sum of the current selection (= sum([Sales])
The operators
+ : union of sets
*: intersection of sets
- : Exclusion of two sets
/ : members belonging to only one of the two sets
If you use the operators with the equal sign:
<Dimension += another set>: add the set to the current selection
<Dimension -= another set>: 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)
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)
The sets that we will study are between angle brackets : <>. They are quite more complex also. But you may use also the operators:
{<set 1> -<set 2>} or {set 1}-{set 2}: members in the set1 that are not in the set2 (because we remove them)
The modifiers
Yes, that is the tricky part of the Sets Analysis ! With them, you can reduce or enlarge the scope of the
selection used in an aggregation. You may modify the selection for one or several dimensions.
A set analysis is not like clicking in the ListBoxes. If you decide to set a dimension to a specific value
currently not selected:
- In the ListBoxes, you will get a new selection
- In the set analysis, you will NOT: you will just set this dimension to this member (and the
intersection with the current selection may be empty)
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.
All members
{Starting selection <Dimension1 = {*} >} ({*} for numeric, {"*"} for text)
Or:
{Starting selection <Dimension1 = >}
The starting selection may be the current one ($ by default) or the complete application 1 or the name of a group or a bookmark .
If you want all members of one dimension, you can write at the right of the equal sign:
- {*} or {"*"} if the dimension is text: selects all members except the NULL
- nothing As you can see below, the {*} can be omitted.
Ex: All MANUFACTURERs but one CATEGORY
{<MANUFACTURER_LDESC = {"*"}, CATEGORY_LDESC={"ACC"} >}
{<MANUFACTURER_LDESC =, CATEGORY_LDESC={"ACC"} >}
Take care if you have created several ListBoxes on the same logical dimension, like TIME. If you
have a Listbox for Month and one for Year, please keep in mind that the user can select on both fields, and
therefore limit the scope of Time by these two fields.
Check the difference between these 2 sets:
{<YEAR = {2012}>} will get the year 2012 but only for the months that have been selected in the ListBox
(that is not the Total of the Year 2012)
{<MONTH=, YEAR = {2012}>} will get ALL months of the year 2012
Each time you have a hierarchical dimension and display several Listboxes, take care to reset to all every dimension except the one you want to select. By setting a value of one dimension, you do not select any associated members (as QlikView does naturally with the ListBoxes).
Known members
Syntax :
{Selection <Dimension1 = {member 1, member 2, …} [, Dimension2 = {member 1,
member 2, …}] >}
Ex : {<MANUFACTURER_LDESC = {AMBOISE, BELLE, AUTREMENT}>}
Ex : {<MANUFACTURER_LDESC = {AMBOISE, BELLE, AUTREMENT}, CATEGORY_LDESC={ACC}>}
In the global syntax: sum({<MANUFACTURER_LDESC = {AMBOISE, BELLE, AUTREMENT}>} [Value Sales])
Please note that:
- The selection may be the current one ($ sign), the database (1), a bookmark or a group. The current
selection is the default selection, the $ may be omitted.
- There is no comma (,) between the set and the measure name
- The dimension or the measure name must be enclosed into brackets [] if the name contains specific
characters like a space, a hyphen (-) …
- Members are always enclosed into curly brackets {} : whatever the way to find them (by name, by
search string, by a function)
- Named members are separated with a comma (,)
- Numeric member names are not enclosed into single or double quotes
- Text members are enclosed into single or double quotes but not necessarily if they do not contain
specific characters like spaces
Ex: {<MANUFACTURER_LDESC = {"AMBOISE","BELLE"}, CATEGORY_LDESC={"ACC", "COLD PLATES
”}>}
Ex: {<MANUFACTURER_KEY = {253, 2789, 1200}, CATEGORY_LDESC={"ACC", "COLD PLATES”}>}
If we do not write anything between the curly brackets, the set is empty:
Ex: {<MANUFACTURER_LDESC = {}, CATEGORY_LDESC={ACC} >}
If we compute some flags (1 or 0 according to a test) into the script, we may use them easily in the set
analysis:
Sum({<Flag = {1}, Year={2014}, Month= >} Sales)
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 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 thesefields.
Limitation:take care, 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 NOTwork. Change the way to compute your data (model, if statement) but forget the set analysis.
The identifier
0 – empty set
1 – complete application
$ - current selection (take care that sometimes your chart or table does not use the current selection but an
Alternate States)
$1 – previous selection ($2: the second previous selection, etc.)
$_1 – next selection
Bookmark01 – name or ID of a bookmark
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 completly reset to All)
Sum({$} [Sales]) : sum of the current selection (= sum([Sales])
The operators
+ : union of sets
*: intersection of sets
- : Exclusion of two sets
/ : members belonging to only one of the two sets
If you use the operators with the equal sign:
<Dimension += another set>: add the set to the current selection
<Dimension -= another set>: 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)
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)
The sets that we will study are between angle brackets : <>. They are quite more complex also. But you may use also the operators:
{<set 1> -<set 2>} or {set 1}-{set 2}: members in the set1 that are not in the set2 (because we remove them)
The modifiers
Yes, that is the tricky part of the Sets Analysis ! With them, you can reduce or enlarge the scope of the
selection used in an aggregation. You may modify the selection for one or several dimensions.
A set analysis is not like clicking in the ListBoxes. If you decide to set a dimension to a specific value
currently not selected:
- In the ListBoxes, you will get a new selection
- In the set analysis, you will NOT: you will just set this dimension to this member (and the
intersection with the current selection may be empty)
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.
All members
{Starting selection <Dimension1 = {*} >} ({*} for numeric, {"*"} for text)
Or:
{Starting selection <Dimension1 = >}
The starting selection may be the current one ($ by default) or the complete application 1 or the name of a group or a bookmark .
If you want all members of one dimension, you can write at the right of the equal sign:
- {*} or {"*"} if the dimension is text: selects all members except the NULL
- nothing As you can see below, the {*} can be omitted.
Ex: All MANUFACTURERs but one CATEGORY
{<MANUFACTURER_LDESC = {"*"}, CATEGORY_LDESC={"ACC"} >}
{<MANUFACTURER_LDESC =, CATEGORY_LDESC={"ACC"} >}
Take care if you have created several ListBoxes on the same logical dimension, like TIME. If you
have a Listbox for Month and one for Year, please keep in mind that the user can select on both fields, and
therefore limit the scope of Time by these two fields.
Check the difference between these 2 sets:
{<YEAR = {2012}>} will get the year 2012 but only for the months that have been selected in the ListBox
(that is not the Total of the Year 2012)
{<MONTH=, YEAR = {2012}>} will get ALL months of the year 2012
Each time you have a hierarchical dimension and display several Listboxes, take care to reset to all every dimension except the one you want to select. By setting a value of one dimension, you do not select any associated members (as QlikView does naturally with the ListBoxes).
Known members
Syntax :
{Selection <Dimension1 = {member 1, member 2, …} [, Dimension2 = {member 1,
member 2, …}] >}
Ex : {<MANUFACTURER_LDESC = {AMBOISE, BELLE, AUTREMENT}>}
Ex : {<MANUFACTURER_LDESC = {AMBOISE, BELLE, AUTREMENT}, CATEGORY_LDESC={ACC}>}
In the global syntax: sum({<MANUFACTURER_LDESC = {AMBOISE, BELLE, AUTREMENT}>} [Value Sales])
Please note that:
- The selection may be the current one ($ sign), the database (1), a bookmark or a group. The current
selection is the default selection, the $ may be omitted.
- There is no comma (,) between the set and the measure name
- The dimension or the measure name must be enclosed into brackets [] if the name contains specific
characters like a space, a hyphen (-) …
- Members are always enclosed into curly brackets {} : whatever the way to find them (by name, by
search string, by a function)
- Named members are separated with a comma (,)
- Numeric member names are not enclosed into single or double quotes
- Text members are enclosed into single or double quotes but not necessarily if they do not contain
specific characters like spaces
Ex: {<MANUFACTURER_LDESC = {"AMBOISE","BELLE"}, CATEGORY_LDESC={"ACC", "COLD PLATES
”}>}
Ex: {<MANUFACTURER_KEY = {253, 2789, 1200}, CATEGORY_LDESC={"ACC", "COLD PLATES”}>}
If we do not write anything between the curly brackets, the set is empty:
Ex: {<MANUFACTURER_LDESC = {}, CATEGORY_LDESC={ACC} >}
If we compute some flags (1 or 0 according to a test) into the script, we may use them easily in the set
analysis:
Sum({<Flag = {1}, Year={2014}, Month= >} Sales)
No comments:
Post a Comment