Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Romancing with Set Analysis

rajeshvaswani77
Valued Contributor III

Romancing with Set Analysis

set-analysis-image-for-blog.jpg

Most of the QlikView Consultants think that Set Analysis is a complex feature to use in QlikView. This blog intends to change this notion and make Set Analysis look something easy to use rather than complex.

Back in our school days we learned set theory in mathematics  We used to draw Venn diagrams that showed the sets as circles and displayed the commonality as an intersection. The QlikView set analysis is based on the set theory.


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.


Simple example that uses set analysis to display the Sum of Sales for a Year


1)Determine the field that needs to be aggregated.

Sales in our case.

2)Determine the type of aggregation that is required.

Sum in our case.

expression - Sum(Sales)

3)Lets change the expression to set analysis

Add Curly brackets

expression - Sum({} Sales)

4)Add a identifier $ ($ represents the records in the current selection)

expression - Sum({$} Sales)

5)Add Angle brackets

expression - Sum({$<>} Sales)

6)Add a Modifier in between the angle brackets

expression - Sum({$<Year={2008}>} Sales)

The simple set analysis expression is now ready. The expression will give us a sum of sales for the year 2008 with respect to the current selection.

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

example - sum({1) Sales) will return total sales in the dashboard irrespective of the selection.

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.

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({$<Year={2010}, Region={“US”}>} Sales)

This will give the sum of sales for year 2010 and region US. Note that Year and Region are modifiers in the expression.

The Set Analysis Wizard created by Stefan Walther could be found at link Set Analysis Wizard for QlikView | qlikblog.at

Hope this basic post on set analysis is helpful.

Comments
Sajid_Mahmood
Contributor

This is excellent and easy to understand the underlying power of set analysis

rajeshvaswani77
Valued Contributor III

Thanks Sajid, I hope to come up with some more soon.

regards,

Rajesh Vaswani

m2r4miller
New Contributor III

Excellent, thank you!

The link to the Wizard has changed, though. The Set Analysis Wizard can be found here:

http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=

rajeshvaswani77
Valued Contributor III

Thanks Mark, Would update the same.

regards,

Rajesh Vaswani

rajeshvaswani77
Valued Contributor III

Updated Link for the Set Analysis Wizard.

regards,

Rajesh Vaswani

Not applicable

To show how it will look in qlikview, please make an example file for this and include one example each for levels - "intermediate" and "expert". Thanks.

Karim_Khan
Contributor III

This is really helpful

Not applicable

Thanks Rajesh

Not applicable

Hi Rajesh

does set Analysis work with function like the one below although its giving me an error.

=Count(DISTINCT{$<[Closing date]={<=Today(),'[Candidate FullName]'}>} Spec)

on the above expression,I  want it to return list of spec that are due from today onwards

Regards

priscilla

hirishv7
Honored Contributor

Nice helpful.

Version history
Revision #:
1 of 1
Last update:
‎09-06-2013 01:45 AM
Updated by: