topic Using Brackets to Define Precedence in Set Analysis in QlikView

Using Brackets to Define Precedence in Set Analysis

Wed, 05 Mar 2014 11:11:53 GMT

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.

Edit: Added specific example

Re: Using Brackets to Define Precedence in Set Analysis

tresB — Wed, 05 Mar 2014 11:17:38 GMT

Probably, correcting the parenthesis would work. Try like:

count(

* (<Name = P({$<Group = {'X'}>})>

- <Name = P({$<Group = {'Z'}>})>)

}

DISTINCT Name

)

Re: Re: Using Brackets to Define Precedence in Set Analysis

Wed, 05 Mar 2014 11:48:42 GMT

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.