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 a commonly used tool when creating advanced formulas in Qlik Sense or QlikView. The basics are fairly simple, but it’s clear that some aspects of it are very complex. In this post, I will try to answer some simple questions that touch the core of the complexity, such as: Can all sets have set modifiers? Are some sets more fundamental than others?

All sets are not equal. Some sets are indeed more fundamental than others.

 

Apart from the two trivial sets {0} (nothing) and {1} (everything), the most fundamental group of sets are the Natural Sets. These are the sets that can be defined as a selection or through a Set modifier, e.g.

 

    {$}

    {$<Country={France}>}

    {BM01<Product=>}

 

Compare these to the non-natural sets, where set operators are used for the set definitions:

 

    {1-$}

    {BM01*$}

 

To understand the difference between the two, you need to know the difference between element sets (lists of distinct field values) and record sets (lists of records in the internal database). See Why is it called Set Analysis?‌.

 

Internally, all sets are stored as element sets and/or record sets in state vectors. For Natural sets both the element sets and the record set can be stored, but for the non-natural sets only the record set can be stored. The reason is that there are no well-defined element sets for non-natural sets.

 

Consider for instance the following table where you want to sum numbers that are not assigned to a customer:

 

Companies.png

 

With Set analysis, this is straightforward. The second expression calculates exactly this, using a non-natural set:

 

Pivot.png

 

But if you try to create the same filter using a selection in the list boxes, you will find that it is not possible - Null is not selectable.

 

List boxes.png

 

No matter how you select from the two list boxes, you cannot recreate this filter. Hence, a non-natural set cannot always be defined through a selection, and thus cannot be stored as element sets.

 

From a logical perspective, you could say that Natural sets are always defined by the element sets (the state vectors of the symbol tables), while the record set (the state vector of the data table) is just the result of the logical inference. For non-natural sets, it is the other way around: These are defined by the record set.

 

This has a number of consequences. First, it is not possible to use a set expression like

 

    { (BM01 * BM02) <Field={x,y}> }

 

where the normal (round) brackets imply that the intersection between BM01 and BM02 should be evaluated before the set modifier is applied. The reason is of course that there is no element set that can be modified.

 

Further, you cannot use non-natural sets inside the P() and E() functions. The reason is that these functions return an element set, but it is not possible to deduce that from the record set of a non-natural set.

 

Finally, a measure cannot always be attributed to the right dimensional value if a non-natural set is used. For example, in the chart below, you can see that some excluded sales numbers are attributed to the correct ProductCategories, whereas others have NULL as ProductCategory.

 

Misassignment.png

 

Whether the assignment is correctly made or not, depends on the data model. In this specific case, the number cannot be assigned if it pertains to a country that is excluded by the selection.

 

Summary:

  • Only natural sets can be re-created as interactive selections and stored in bookmarks
  • Only natural sets can be modified
  • Only natural sets can be used inside the P() and E() functions
  • A measure cannot always be attributed to the correct dimensional value if a non-natural set is used

 

HIC

 

Further reading related to this topic:

Why is it called Set Analysis?

Colors, States and State vectors

Symbol Tables and Bit-Stuffed Pointers

10 Comments
rbecher
MVP
MVP

Dear Henric,

thanks for your post! This is very interesting, as always.

I have a question. Is there any practical use for the trivial set {0} (nothing)?

Best regards,

Ralf

3,516 Views
hic
Former Employee
Former Employee

Short answer: No.

It cannot be modified, since also this set doesn't have an element set.

HIC

3,516 Views
swuehl
MVP
MVP

Very nice post!

Didn't even know that {0} exist, but since there is no practical use, I haven't missed it so far.

One question (though I should just try it later today):

What happens if you are trying to use a non-natural set within e.g. a p() function? You just get incorrect results or no results? Or an error message (I don't think so)?

Stefan

3,516 Views
hic
Former Employee
Former Employee

i need to test it to be completely sure, but I suspect it takes the first set in the set expression and neglects the rest...

HIC

0 Likes
3,516 Views
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Very interesting post!

I'm wondering if there are any practical implications about natural vs. non-natural sets - any tangible performance differences, for example? If non-natural sets are defined by "record sets" as opposed to "element sets" - does it make them heavier in terms of performance and memory consumption?

In other words - what practical implications should we be aware of, besides the terminology and the limitation within the P() and E() functions?

thanks!

Oleg Troyansky

3,516 Views
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

I tested the behavior of the P() function with a non-natural set, and it's quite bizzaire - depending on the selections, the filter either appears to be ignored, or return completely unexpected results.

3,516 Views
chrisg
Partner - Creator III
Partner - Creator III

Thx - a very nice post!

0 Likes
2,880 Views
Saravanan_Desingh

Hi hic

So, the below format is not a valid one?

{<..>}+{<..>}

I mean adding two record sets..

I know the below format is valid..

{<..>+<..>}

0 Likes
2,880 Views
hic
Former Employee
Former Employee

Correct.

{<..>}+{<..>} is an incorrect syntax. (The entire set analysis expression must be enclosed in curly brackets.)

{<..>+<..>} is a correct syntax describing the union of two sets.


HIC

2,880 Views
gballester
Creator
Creator

Hi,

I have to create a third bookmark from two existing bookamrks as union or intersection.

I can't think of a way, has someone already had to do something similar?

Using a set operation between both bookmarks in an set analysis  is very simple, but creating a third bookmark from those two previous ones, I can't think of how.

Thanks

1,382 Views