Discussion Board for collaboration related to QlikView App Development.
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:
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:
+----------+-----------------------+-----------------+ | 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!
Solved! Go to 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).
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.
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.
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)
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).