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.