Using Brackets to Define Precedence in Set Analysis
I am trying to get a distinct count of people belonging to group X and Y but not Z. People can belong to multiple groups and the data structure is such that there are two columns and people can have multiple records, e.g:
Name Group
A X
A Y
A Z
B Y
C X
C Y
So in this example the result should be equal to 1, since only C satisfies the condition.
However, I am not sure how to specify the order such that QlikView calculates it as ((A and B) but not C) rather than (A and (B but not C))
To get A and B I use:
count(
{$
<Name = P({$<Group = {'Y'}>})>
* <Name = P({$<Group = {'X'}>})>
}
DISTINCT Name
)
And adding the not C should be:
count(
{$
<Name = P({$<Group = {'Y'}>})>
* <Name = P({$<Group = {'X'}>})>
- <Name = P({$<Group = {'Z'}>})>
}
DISTINCT Name
)
In this case the precedence appears to work out the terms in the order I want, and the qlikview help on set analysis suggests that this would be the case as intersections are evaluated before exclusions. However, as a general rule I would like to be able to use brackets to be certain, and for more complex cases which go against the default precedence. Yet if I wrap the first two terms in brackets it does not evaluate at all:
count(
{$
<Name = P({$<Group = {'Y'}>})>
( * <Name = P({$<Group = {'X'}>})>
- <Name = P({$<Group = {'Z'}>})>)
}
DISTINCT Name
)
How can I get this working? The set analysis help states that normal brackets should work, but in the examples it uses this inside a single term, and does not work for what I want to do.
Very interesting, I hadn't actually wanted the brackets around the latter terms, but your suggestion did produce a valid result in its own right.
But in some cases I would need to use the brackets on the first terms so was wondering how to do this. I tested further and it seems that QlikView doesn't like a bracket right at the start of the sequence. So I tested by adding a dummy term at the start which always evaluates as true, then intersected this with the the remainder of the terms which I enclosed in brackets in the order I had wanted.
Specifically I used:
count(
{$ <Name = >
*
((<Name = P({$<Group = {'Y'}>})>
* <Name = P({$<Group = {'X'}>})>)
- <Name = P({$<Group = {'Z'}>})>)
}
DISTINCT Name
)
The blue text is the dummy term, green is the outer brackets used to enclose the remainder and red is the inner brackets which I originally wanted.
I'll test on a more complex dataset to see if the logic works in all cases, but this does appear to be what I needed. Thanks for the help.
Very interesting, I hadn't actually wanted the brackets around the latter terms, but your suggestion did produce a valid result in its own right.
But in some cases I would need to use the brackets on the first terms so was wondering how to do this. I tested further and it seems that QlikView doesn't like a bracket right at the start of the sequence. So I tested by adding a dummy term at the start which always evaluates as true, then intersected this with the the remainder of the terms which I enclosed in brackets in the order I had wanted.
Specifically I used:
count(
{$ <Name = >
*
((<Name = P({$<Group = {'Y'}>})>
* <Name = P({$<Group = {'X'}>})>)
- <Name = P({$<Group = {'Z'}>})>)
}
DISTINCT Name
)
The blue text is the dummy term, green is the outer brackets used to enclose the remainder and red is the inner brackets which I originally wanted.
I'll test on a more complex dataset to see if the logic works in all cases, but this does appear to be what I needed. Thanks for the help.
