qlikview set analysis

 

Qlikview set analysis is an ace in the Qlikview’s stack of cards for a developer. Qlikview’s associative data model works well with your current selections. But there are times when you want to create a set or a group of information independent of your current selection. In other words, a data set that has no influence of your current selections.  A year over year or a month over month sales comparison is a handy example for you to use Qlikview set analysis for.

Like those fun lego games, you can start with all data in your application and then add, remove or change data to create another set that you want.

I admit that the syntax of the set analysis in qlikview is bit intimidating for a beginner, but it’s not as daunting as it looks once you master the concept.

Let’s start with the basics first.

The very word set analysis ought to remind you of a guy named  John Venn. We all have learned Venn diagram in our school days. And, you will revisit this age-old theory again as we learn set analysis in Qlikview.

With that said, set analysis provides a method to create a set based on another set. And, by now it must be obvious to you that it is used in aggregation function.

So, let’s look at the anatomy of a set analysis function in Qlikview, shall we?

lightbulbIt has three major categories: Identifiers, Operators and Modifiers

It starts and ends with a curly bracket: {}

 Example:  

1.  Set analysis begins and ends with { }

2. Modifier begins and ends with <  >

3. Operator:  =

4. Identifier is not shown but it is $ which is the default state in Qlikview.

qlikview set analysis basics

 

For those of you who know SQL, set analysis is akin to a SELECT…. GROUP BY delivered in a concise expression. Armed with this basic knowledge, now you can add many more forms of identifiers, operators and modifiers to create a complex aggregation that otherwise requires many IF statements.

Let’s look at each category and its various options:

1.  IDENTIFIERS:

Identifier defines the state of the set.

$  is the default state, meaning the current selection for a given sheet.

1  ignores the current selection and considers all the values in the data set.

Bookmark10  state defined by a stored bookmark.  If you are new, please review this article to learn more about book marks. 

Now, the fun begins…

You can subtract one state from another. For example,

1 – $  Selects all values from the data set but the current selection.

1- Bookmark10 Selects all values from the data set but those defined by a given bookmark.

MODIFIERS:

Identifier is like a SELECT keyword and Modifier is  like a WHERE clause or a filter in your SQL select statement. It works on the selected data set — based on your identifier — to add, remove or to modify existing selections.

Modifiers start with < and end with >.  In this example, the returned data set gets modified to filter Year with 2005 and 2006 values. Thus, the resulting data set contains only data for those 2 years.

If you want to do a year vs year analysis, modifier allows you to select data set for the previous year. Like identifier, modifier can make otherwise complicated programming a simple task. For instance, if you want to ignore certain field from your data set, you can simply add a modifier with <Field=>.

qlikview set analysis modifiers_2

 

OPERATORS:

Operators are like executors of the Qlikview set analysis. Operators somewhat control behavior of modifier statements. They are like a method of an object as they allow interactions between two different data sets in a single expression.

If nowhere else, John Venn’s diagram lives it legacy when you depict how operators work in Qlikview. Remember that we learned union, exclusion, intersection and symmetric difference in our school days when we learned about the Venn diagram.

Example: sum({$ + Year20052006}  TotalDue)

Qlikview set analysis operators

 

Four basic operators:

qlikview set analysis operands

 

 

Set analysis syntax is intimidating for most beginners, but Stefan has created an excellent set analysis wizard that makes this daunting task easy to deal with.

As you can see, Qlikview set analysis is a powerful way for you to create dynamic data sets mainly for comparison analysis. We have just made our feet wet in the ocean of possibilities with this introduction.

In the next Qlikview tutorial, we will try to swim together with some advance techniques using dollar sign expansion. Till then, so long…

Elsewhere:

Qlikview Set Analysis via Qlikview community

The Magic of Set Analysis via iQlik

photo by: knitvana


If you enjoyed this article, get email updates (it’s free).

Email Address:

Related Posts Plugin for WordPress, Blogger...