I'm having trouble mentally modelling how set analysis & nested aggregation work together, because I can't find an explanation for the behaviour I'm observing.
My table model is just a single, denormalised table. I have a pivot chart that, in general terms, is like this. It has
* dimensions D1, D2, ... Dn
* an expression like
SUM ({set_expr_1}
AGGR (
if (
SUM ({set_expr_2} expr) > threshold,
1,
0
),
d1, d2, ..., dk // these are a superset of the chart's dimensions
)
As you can tell, I'm basically trying to count how many dimension combinations meet some criterion based on a SUM.
So, question 1: how does each set expression impact the other? My mental model - which isn't working - has been that
- QlikView should evaluate set_expr_1, which specifies a set of field-selection combinations, which are then applied to the incoming set of records (which is all of them at this point, unless '$' has been specified explicitly or by default), resulting in the set S1 of all records that result from those combinations;
- the outer SUM should pass that set of records, S1, through to the AGGR
- the AGGR should pass that set of records through to the inner SUM.
- the inner SUM should evaluate set_expr_2, which specifies another set of field-selection combinations, which are then applied to the incoming set of records (i.e. S1), resulting in what is effectively the intersection of the two set expressions, resulting in the set S2.
- the inner SUM is evaluated against S2 for each combination of dimensions specified in the enclosing aggr (i.e. d1, d2, ..., dk), resulting in an internal, temporary table of (d1,d2,...,dk)-->innersumresults.
- this table is passed to the outer SUM, which now totals the innersumresult for each combination of values of the dimensions specified in the enclosing chart (i.e. D1, D2, ..., Dn), resulting in the chart result of D1, D2,...,Dn -->outersumresults
- the chart displays this result.
The reason this isn't working for me is that both my set expressions are of the form {1< field1={'value1'}, field2={'value2'}, ... >} (so, we're attempting start with the full set of data and explicitly select subsets), and yet the resulting chart is being sensitive to user selections. I thought it could be a Chart Property thing, and tried marking the checkboxes for 'Show All Values' for all the dimensions, but this didn't help. So, I'm thinking my mental model of how set expressions interact is flawed.
Can anyone correct my thinking, here?
Thanks,
Angus.
