Set Analysis In QlikView

                                                   QlikView has a feature called SET ANALYSIS that directs us a way to add context to a dashboard. When we make selections on certain variables, the changes will be reflected in the entire application.

Set analysis is an extremely powerful feature of qlikview, and it’s important that qlikview developers have some exposure to it, and eventually a thorough understanding of set analysis. Set analysis is a powerful tool for comparison . This section serves as an overview of set analysis and a suggestion to explore this feature in greater detail.

Features of Set analysis are:

• Must be used in aggregation functions
• Expressions always begins and ends with curly brackets {}
• Used to create a different selection compared to the current selection.
• Modifier begins and ends with <>
• Operator =
• Identifier is $ by default

Set analysis allows users to compare different sets of data (comparison analysis), without the use of complicated scripting or creating very complex expressions in the chart objects. The data returned by a set analysis, expression can be independent of any user selections, and opens up the ability to select all data loaded in qlikview, regardless of current selections. The set is defined by the developer, and may be a bookmark or function of a user selection (such as the inverse of current selections, full data set, and previous set). Some examples of this in use are:

• products purchased this year versus last year
• sales of a selected sales representative versus the top sales representative

Set Analysis Syntax

Set expressions are always contained in curly brackets:

{ }

A set expression can contain the following elements:

• set identifier
• set operator
• set modifier

1. Set Identifier

The basic element in a set analysis statement is a set identifier. The term identifier refers to identifying the active set . The set identifier is the state of the data set, and is the first element in the set statement (after the curly brackets). The syntax usage is as follows, for example, the sum of sales:

Sum ({set identifier}   sales)

There are four common set identifier options:

* {$}: this option designates the current selection set (the default set). An example that shows the sum of sales for the current selection:

Sum ( {$} sales)

* {1}: this option indicates the full set, ignores the current selection, and considers all data. An example that shows the sum of sales for all data:

Sum ( {1}   sales)

* {any book mark}: this option indicates a bookmark (named anything) selection set. An example that shows the sum of sales for the selections saved with the bookmark named 2006 sales:

Sum  ({2006 sales}  sales)

* {$1}: this option designates the previous selection set. Note that the expression  {$0} is the current selection and {$-1} is the forward selection (if a user clicked on the back button). An example that shows the sum of sales for the previous selections:

Sum( {$1}  sales)

* {$-1}: this option designates the forward selection set.

2. Set Operators

Set operators perform functions on the selected selection and any set identifier.  Operations are evaluated from left to right. In the absence of any standard brackets to control how the expression is evaluated, intersection and symmetric are evaluated first, followed by union and exclusion. Note that qlik tech advises that the use of operators used with basic aggregation function (such as sum) on data fields from different tables may have unpredictable results and should be avoided.

There are four common set operators:

* the + operator indicates the union between two sets (returns all data from both sets), such as an example of the sum of sales where the sales are in the union of the current selection (indicated by $) and the bookmark called any bookmark:

Sum (  {$  +  any book mark}  sales)

* the – operator indicates the exclusion of the selection, such as an example where the sum of sales is calculated for everything (indicated by 1) except for the current selection (indicated by $):

Sum ({1-$} sales)

* the * operator indicates the intersection between the two sets (returns data in common to both sets), such as an example where the sum of sales is calculated for any data in common between the current selection (indicated by $) and a bookmark named any bookmark:

Sum ({$  * any bookmark}   sales)

* the / operator indicates a symmetric difference between the two sets (returns data from either set, but not data in common between the two sets), such as an example where the sum of sales is calculated for any data contained (but not shared) in the current selection (indicated by $) and a bookmark named any bookmark:

Sum ($ / any book mark}  sales)

3. Set Modifiers

Set modifiers are always enclosed by angle brackets, <>. The term modifiers are used to modify the base set based on given conditions. Set the modifiers modify the existing selection, and are equivalent to a WHERE clause in SQL, which serves to isolate or narrow down a dataset; using a set modifier is like making a selection in qlik view.They make additional alterations.

The best way to illustrate the concept of set modifiers is by working through examples. Note that in set modifiers, you can also use search syntax such as>,<, and>=. Some examples of set modifiers are:

*sum ({$ < year= {2012}   >}  sales): the sum of sales for the current selections for the year 2012.

*sum ({alternate state 1 < year = {2012}>} sales): the sum of sales for the year 2012 and the selections made in list boxes designated as an alternate state named alternate state 1.

*sum ({ $ < year= year – {2011} >} sales): the sum of the sales for the selected year (s) (noted by $), but excluding 2011.

*sum ( {$  < product = {“ *”}  >} sales): the sum of the sales for all products (wild card character denoting all) for the selections.

*sum  ({< product  = >} sales): the sum of sales for the selected fields, but ignoring the product field selection.

*sum ({$  < product = { }>} sales): the sum of sales for the selected fields, but for anything not associated with the selection in the product field.

*sum  ($<year  = {$ ( = $ (v year)) }> } sales): using the dollar sign expansion for the v year variable, this is the sum of sales for the year specified in the variable. An example of this is if the v year variable was set to the expression

= year (today ()).

*sum ( { 1<year = { “20*”, 1999}-{2001}>}  sales): the sum of sales for all year in the full data set (1) for the years starting with 20(wild card*), and 1999, but excluding 2001 (minus exclusion operator before 2001).

*sum ({  $<product = { “*laptop*”} >}  sales): the sum of sales for all selections for product containing the string laptop (by using the wild card * around the string laptop).

*sum ({ $ <year = year + {2012, 2013}>} sales): the sum of sales for the selected year, plus the years 2012 and 2013 (this is a union operation).

*sum ({ $ <year= {2011,2012}, zone= {“Europe”}> } sales): the sum of sales for the years 2011 and 2012, in the Europe zone.

*sum ({ $ < customer= (p ( { 1< product category= {“ instruments” } >}))>} sales) : the p element here indicates all possible data. The other option for an element is E, which is excluded and the opposite effect of possible. This is the sum of sales for all customers who ordered any products of the instrument product category. To omit selections made against the field, moderator can be left empty.

Logically, in QlikView set expression begins with SET IDENTIFIER, then SET MODIFIER and finally SET operator.

Ex:- 

I am trying to display YTD(Year to Date) which should show the value for "all" the months for the selected year.

If you want to return avg for more than 1 selected year, you will need to put the GetFieldSelections through a variable as it needs the quote marks adding in, eg ...

variable = =Replace(GetFieldSelections(Year),', ','","') ( - the quotes at the end spread out are ... (Year), ',' , ' "," ')

then reference this in your expression ...

=num(avg({1<Year={"$(variable)"}>} S14),'0.00')

If you only need to return for one year at a time then this should do the trick, multiple years will not work ...

=num(avg({1<Year={'$(=getfieldselections(Year))'}>} S14),'0.00')