Count with Set Analysis

[ovcharenko]

Hi,

There is a table

LOAD * INLINE [

F1, F2, F3

1, 1, 0

2, 1, 0

3, 1, 1

4, 0, 1

5, 0, 1

6, 0, 1

7, 0, 0

8, 0, 0

9, 0, 0

];

variant 1

In text box

='Count where F1>=2 and (F2=1 or F3=1) is '&Count({$<F1={">=2"}>*($<F2={1}>+$<F3={1}>)} distinct F1) &

'They are: '&concat({$<F1={">=2"}>*($<F2={1}>+$<F3={1}>)} distinct F1,' ')

result:

Count where F1>=2 and (F2=1 or F3=1) is 8

They are: 2,3,4,5,6

variant 2

In text box

='Count where (F1>=2 and F2=1) or (F1>=2 and F3=1) is '&count({$<F1={">=2"},F2={1}>+$<F1={">=2"},F3={1}>} distinct F1)&

'They are ' & concat({$<F1={">=2"},F2={1}>+$<F1={">=2"},F3={1}>} F1,' ')

result:

Count where (F1>=2 and F2=1) or (F1>=2 and F3=1) is 2

They are: 2,3,4,5,6

Why so?

Thanks in advance,

Sergey

[Miguel_Angel_Baeyens]

Hello Sergey,

Is this some kind of excercise? There was somebody asking the same exact document and inline sample table few weeks ago.

Anyway, I'd do as follows

count({< F1 = {'>=2'}, F2 = {1} > + < F1 = {'>=2'}, F3 = {1} >} F1)

I'm not using distinct because there are values valid for both set modifiers, and the set analysis is just one. Setting "distinct" evaluates each set modifier separately, and returns the correct value (in your example, the 3 returned by the first modifier is different than the 3 returned by the second modifier). Although I don't know the "guts" of QlikView, if you do a truth table with the values you have, and count all "trues" returned you will have one for 3 (F2 = 1, F3 = 0), one for 3 (F2 = 0, F3 = 1) and one for 3 (F2 = 1, F3 = 1). Forcing "distinct" sounds like there are three trues of 3, although we only want to count them as one.

Please, some QlikTech tech can correct me if anything?

EDIT: I haven't tested, but it seems that removing distinct from Concat does not have any impact on the results, which seems to me logical since they are not actual repeated values for F1 field.

[ovcharenko · Accepted solution]

It is an error and in a version 10 she was corrected