2 Replies Latest reply: Aug 8, 2011 2:52 AM by Sergey Ovcharenko

# Count with Set Analysis

Hi,

There is a table

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?

Sergey

• ###### Count with Set Analysis

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.

• ###### Count with Set Analysis

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