Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

Set analysis is one of the more complex things you can use in QlikView or Qlik Sense. Its syntax is often perceived as complicated and there are some misunderstandings around it. So here is my short explanation.

Set analysis is a way to define an aggregation scope different from current selection. Think of it as a way to define a conditional aggregation. The condition, however, is in itself like a selection that is evaluated before the cube (the chart) is expanded. Hence, it is not possible to have a condition that is evaluated row by row.

 

The set analysis is used inside an aggregation function, e.g. in an expression like

 

          Sum(Sales)

 

The first step is to add the markers for the set analysis – the curly brackets:

 

          Sum( {…} Sales)

 

These define the record set over which the aggregation should be made. Inside, you can use different identifiers and operators, e.g. ‘$’ for records implied by current selection, ‘1’ for all records, ‘1-$’ for all excluded records, etc.

 

A set of records that you can define by a simple selection is called natural set. Not all record sets are natural; for instance, {1-$} cannot always be defined through a selection.

 

The next step is often to add a set modifier, which is defined by angle brackets. The set modifier adds or changes a selection. It can be used on any natural set and consists of a list of fields, where each field can have a new selection:

 

          Sum( {$<…>} Sales)

 

The next step is to define the element set for a field; the set of field values that defines the selection. The element set could be a field reference or a set function, P() or E(). It is more common, though, that it is an explicit list of field values or a search, and then you need the curly brackets to define the element set:

 

          Sum( {$<Date={…}>} Sales)   or   Sum( {$<Date=P(…)>} Sales)

 

A search can be defined through double quotes. This way, field values that match the search string will be selected:

 

          Sum( {$<Date={"…"}>} Sales)

 

Do not use single quotes to initiate a search here! Single quotes should denote literals, i.e. explicit field values. (Yes, today single quotes work as a search, but this is a bug that one day will be fixed…)

 

Often a numeric search is made, defined by a leading relational operator. Then, field values will be selected based on a comparison:

 

          Sum( {$<Date={"<=…"}>} Sales)

 

To make it worse, the value to which the field values are compared is often a calculated one. And in order to get a calculated value into the expression, a dollar expansion is needed.:

 

          Sum( {$<Date={"<=$(…)"}>} Sales)

 

Inside the dollar expansion, you need an expression that starts with an equals sign and contains an aggregation function, e.g.:

 

          Sum( {$<Date={"<=$(=Max(Date))"}>} Sales)

 

This aggregation function is evaluated globally, before the cube is expanded.

 

As you can see, there are many levels of a set expression, and many pairs of brackets and delimiters that need to match. When you write set expressions, you should always write both brackets directly, and then continue with the expression between them. This way you will avoid simple syntax errors.

 

Good luck with your set analysis expressions!

 

HIC

 

Further reading related to this topic:

Why is it called Set Analysis?

Dates in Set Analysis

Excluding values in Set Analysis

Introduction to Set Analysis (video) - Part 1

39 Comments
kkkumar82
Specialist III
Specialist III

Hi HIC,

Can we use ? in set analysis, if so, what is the use , can you explain.

thanks

KK

0 Likes
13,258 Views
hic
Former Employee
Former Employee

You can use a question mark as a single-letter wildcard if you make a set analysis with a text search. For example, the following expression will sum the sales for products that have 'x' as the second character in the product name:

Sum( {$<Product={"?x*"}>} Sales)

HIC

13,258 Views
kkkumar82
Specialist III
Specialist III

Thanks

for reply.

KK

0 Likes
13,220 Views
anderseriksson
Partner - Specialist
Partner - Specialist

Hi Hendrik, you obviusly have not grasped the search functionality in set analysis.

Your expression with the year range:

SUM({<YEAR=>}If(YEAR>=$(vStartYear) AND YEAR<=$(vEndYear),SALES))

would be better like this using set analysis finding all years in your intervall:

SUM({<YEAR={">=$(vStartYear)<=$(vEndYear)"}>} SALES)

Using If in your expressions is one of the top reasons an expression is heavy to calculate and takes much CPU.

When the If statement does not make use of the dimension values you are better of using set analysis.

If you do have a condition dependent on the dimension values unfortunately you can't use set analysis.

From that combining the two is easy:

SUM({<YEAR={">=$(vStartYear)<=$(vEndYear)"}, PRODUCT={"Itemfield_inside_PRODUCT"}>} SALES)


Note that you should use " instead of ' as Henric has pointed out numerous times.


Personally I would not use variables for the years.

If you have a list box for the years why not simply let the users select the range of years they want to view?

Triggers are best avoided as they inflict extra calculations each time fired.

0 Likes
13,220 Views
Not applicable

Hi Henric,

As I understand the set analysis is always about filtering a field by a value, either is it a static value or a parameter, But is it possible to filter a field by another field in set analysis?

For example I have a table as below:

Flag1   Flag2  Day

1          0         1/6/1999

0          0          2/6/2012

0          1          2/6/2006

I need to do set analysis to filter this table on "Flag1 <> Flag2", is there an easy way to do it?

BR, P Q

0 Likes
13,220 Views
Not applicable

Thank you, very helpful.

0 Likes
13,220 Views
hic
Former Employee
Former Employee

No, that would be a row-based evaluation, and that cannot be done. You could, however, define a new field "Equality" in the script, e.g.

     Load Flag1, Flag2, Day, If(Flag1=Flag2,1,0) as Equality From ...

and then use this flag in Set Analysis.

HIC

13,220 Views
Not applicable

Thanks a lot Henric!

0 Likes
13,220 Views
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

Comprehensive blog post for new and experience users of Qlikview.

Good job, Henric!

0 Likes
13,191 Views
Not applicable

Great article, it clear out many of my current doubts!

0 Likes
13,191 Views