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

    Count with Set Analysis

    Sergey 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

       

        • Count with Set Analysis
          Miguel Angel Baeyens de Arce

          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
            Sergey Ovcharenko

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