QlikView – Set Analysis and Alternate State

QlikView Set Analysis allows you to perform flexible aggregation.

Usually aggregation occurs on the set of values in the current selection.

SetAnalysis2

Aggregation based on current selection Year = 2001

But many a times business requirements demand to perform aggregation on a Set of records

that are independent of current selection.

For e.g. your current selection may be Year = 2001, but using set Analysis you can aggregate records where Year = 2002 or where Year <> 2001.

About Set Analysis

  • Set analysis provides the ability to create set of data that is independent of current selection.
  • It is used in aggregation function.
  • Always starts and ends with curly brackets.

Set Analysis uses the following in the syntax :

  • Identifiers
  • Operators
  • Modifiers

Using Set Analysis to aggregate on Set of values  independent of current selection

SetAnalysis31

Writing expression to aggregate on set of data independent of current selection

Sum ({$Year = {‘2002’}>TotalDue)

– The above expression will give you Sum (TotalDue) where Year = 2002 even though the current selection is Year = 2001.

– This expression uses set modifier syntax

SetAnalysis122

Set Analysis and Alternate state

Set Analysis can be effectively used with Alternate State to display comparative analysis. Please refer my earlier post to get an overview of Alternate States.

Syntax for using Alternate State with Set Analysis

If you declare your Alternate State as State_1 and use CategoryName as modifier then the syntax is as follows

Sum ({State_1<CategoryName = {‘Accessories’,’Components’,’Clothing’}>TotalDue)

In the similar fashion,Set Analysis can be used with Book marks also.

By using Set Analysis you can create a very functional application.

QlikView Set Analysis syntax can be bit overwhelming for a new developer. Please use Stefan Walther Set Analysis Wizard if you struggle with the syntax. Though with clear concepts and practice it will become easy.

3 thoughts on “QlikView – Set Analysis and Alternate State

  1. Great article but you might want to correct the typo to help people learning this stuff. The example above “Sum ({$Year = {’2002′}>TotalDue” is incomplete. I believe it should be Sum({$}TotalDue)?

    • Chandraish Sinha says:

      Oli:
      Thanks for pointing that out. I have made the corrections.
      Appreciate your help.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.