How does Set Analysis Work in QlikView

Hi

In previous posts I’ve talked about the QlikView Engine Explaining Symbol and Data tables and how QlikView understand Selections using the Logical Inference

Here we will be taking the concept one step further and talk about how QlikView uses these new tables and logic when calculation an expression with Set Analysis.

Set Analysis is merely an additional set of state vectors created by the logical inference engine:

QlikView Set Analysis

QlikView Set Analysis

Here we can see two values within the Product Symbol table are possible in the current selection as their State Vector is 1. The next two columns created by QlikView are as s direct result of the Set Analysis statement $<Product={Boots,Cap}>. The expression would only aggregate values for ‘Boots’ though as this is the only possible State Vector in the example. The reason being is the ‘Cap’ is exclude by another selection. Maybe the ‘Year’ selected by the user is 2015 and they haven’t sold any ‘Caps’ in this time period.

The syntax of a QlikView expressions is:

 Sum(Sales)
  • Aggregation over the records using the normal set space
Sum( {…} Sales)
  • Aggregation over the records using the different set space
  • This first Pair of curly brackets define a Record Set: A set of records in a Data Table
Sum({ $<Month={…}>} Sales)
  • This new pair of curly brackets denotes the Entity Set: A set of values in the symbol table
Sum({ $<Month={“…”}>} Sales)
  • Double Quotes means a search for field values
Sum({ $<Month={“<=$(…)“}>} Sales)
  • A dollar expansion means that something is inserted before the parsing of the expression

Set Expressions are evaluated by the Logical Inference Engine, It is done before aggregation made by the calculation engine. Hence, a set expression can’t be evaluated row-by-row. This can be explained further by looking at the order in which QlikView calculates the expression.

Order Expression Comments
1 Sum( { $<Month= {“<=$(=Max(X))”}>} Sales) Dollar Expansion
2 Sum( { $<Month= {“<=5”}>} Sales) Parsing of Expression
3 Sum( { $<Month= {“<=5”}>} Sales) Set Analysis Expression Evaluated
4 Sum( { $<Month= {“<=5”}>} Sales) Cube Expansion and Aggregations

 

Happy Qliking!

Richard

2 responses to “How does Set Analysis Work in QlikView

  1. Hi Richard, Great post. It doesn’t work for everybody but, for some, understanding a little of the underlying nuts and bolts helps understand how to use set analysis better.

    You have a few left curly braces missing in your example syntax. For example, Sum( $} is missing the first left curly brace.

    Thanks for the blog.
    -Tim (QlikView Maven)

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s