Set Analysis hides rows. Nonsensical aggr unhides them.

[gmenoutis]

I have asked this on stackoverflow, but had no elightening answer, so I am transferring my question here. Although I actually have found how to get what I need, I really want to understand how the tool functions.

I am confused about the logic that exists behind the showing and hiding of rows in charts of QlikView/QLik Sense. Here is what I thought was the case:

1. If, for some row, the value of a dimension is NULL, and for that dimension "Supress NULL" is on (QV) or "Include NULLs" is off(QS), then the row is not shown.
2. If, for some row, all its expressions/measures are zero or NULL, and the object-level setting "Supress Zero Values" is on (QV), or "Include Zero Values" is off (QS), then the row is not shown.
3. The rest of the rows are shown.

However, I get a confusing example of a measure which causes rows to disappear even though I have suppress zero values off/ inlude zero values on. Here is a small script of some sample customers and their consultation:

customer:
LOAD * INLINE [
custcode,descr
C1,pan1
C2,pan2
C3,pan3
];

consultation:
LOAD * INLINE [
custcode,grp,val,x
C2,eye,sth1,1
C2,age,20,1
C3,legs,sth2,1
C3,skin,sth5,1
C3,age,20,1
C3,age,30,1
];

As you can see, custcode C1 has no consultation lines. I proceed to create a straight table with custcode as dimension and sum(x) as measure. Here is what I get:

+----------+--------+
| custcode | sum(x) |
+----------+--------+
| C1       |      0 |
| C2       |      2 |
| C3       |      4 |
+----------+--------+

Everything fine until now. Sure enough I haven't supressed zero values: If I did, the C1 row would get removed. Also, let's note that no aggr is needed for whatever reason.

Now, let's add a set analysis to that measure to only sum x for grp='age':

sum({<grp={'age'}>}x)

This hides row C1 from sight:

+----------+-----------------------+
| custcode | sum({<grp={'age'}>}x) |
+----------+-----------------------+
| C2       |                     1 |
| C3       |                     2 |
+----------+-----------------------+

Question 1: Why does set analysis hide the row in this case?

Adding an additional measure with a value of 1 changes nothing. We can be sure this has nothing to do with zero values settings.

Now, let us add this measure:

aggr(min(0),[custcode])

The row got back, even though the new measure is NULL :

+----------+-----------------------+-------------------------+
| custcode | sum({<grp={'age'}>}x) | aggr(min(0),[custcode]) |
+----------+-----------------------+-------------------------+
| C1       |                     0 |                       - |
| C2       |                     1 |                       - |
| C3       |                     2 |                       - |
+----------+-----------------------+-------------------------+

Now, about aggr, here are two strong reasons why I think it should not be neccessary:

1. If the set analysis measure was wrong and needed to include aggr in some way, this would still be no reason for the engine to hide the rows - it would just return a NULL for having an invalid formula. Also, this measure actually works correct, as we can see
2. custcode is the field which creates the association between the two tables. But this doesn't seem to be the cause for it to unhide rows; actually, I get the same even with aggr(min(0),[]):

+----------+-----------------------+-----------------+
| custcode | sum({<grp={'age'}>}x) | aggr(min(0),[]) |
+----------+-----------------------+-----------------+
| C1       |                     0 |               - |
| C2       |                     1 |               - |
| C3       |                     2 |               - |
+----------+-----------------------+-----------------+

Question 2: Why does this strange aggr measure unhide the row?

Today, one week after I originally posted this question, I had a table functioning strangely. I added alt(aggr(min(0),[],0) to one of the expressions and everything started working perfectly! Please help me on this, my amazement is sky high!

[a_mullick]

Answer to Q1:

The set analysis reduces the dataset before the SUM() is applied. So selecting grp='age' removes any rows with C1 from the dataset before summing. There is simply no result for C1 to suppress in the first place.

[a_mullick]

Re: Q2

When I try your example, the  row for C1 'appears' as you indicate, but that is because the aggr() function returns something for every row, so the NULL value reveals an entry for C1, but if you go back to the Presentation tab and check 'Suppress Zero-Values' then the entry for C1 is indeed supressed.

[gmenoutis]

My impression is that the reduction you mention only happens to the "local" dataset of the specific measure column. Is that wrong then? Does this mean that the visible rows are the ones with dimension values included in the selection (intersection) the union of all measures' set analysis datasets? Is this documented anywhere, I've been throught the manual and never saw something like this.

[sunny_talwar]

I am not sure I can fully explain the behavior, but an easier solution would be to use this expression to show 0 row

sum({<grp={'age'}>}x) + Sum(0)

 

[a_mullick · Accepted solution]

Yes, the set analysis is per column (expression) but in  your first example, there is only one expression. If you added a second expression such as

sum({<custcode={'C1'}>}x)

The row for C1 would show (providing you are not suppressing zero values).