1 Reply Latest reply: Feb 6, 2013 10:08 AM by Juan Gerardo Cabeza Luque RSS

    Set analysis on a key field

      Hi,

       

      I want to create a set analysis for three selections saved as bookmarks (Set1, Set2, Set3).

       

      In my example, I have created three tables of data which are linked by field [nr].

      My problem is that I can not do set analysis and calculate the number based on the key [nr]. Instead, I have to use the respective [nr1], [nr2] and [nr3] for correct set analys result.

       

      Set1:

      1,2,3,4,5,6

       

      Set2:

      1,2,3,7,8,9

       

      Set3:

      3,4,5,7,10,11

       

       

      Count({Set2*Set3}  DISTINCT [nr]) = 1 (should be 2)

       

      Count({(Set3*Set2)}  DISTINCT [nr2]) = 2  (this i correct)

       

       

      Is it not possible to make set analysis and count the number of a key field?

       

       

      Best regards

      Dalle

       

       

       

      Enclosed please find my test project.

        • Re: Set analysis on a key field
          Juan Gerardo Cabeza Luque

          In these cases, I ususally prefer using a "hub" table with flags to easily count or filter the nr values. The script for this table could be this one:

           

          TotalTable:

          Load Distinct

                    1 AS nr1,

                    nr1 as nr

          Resident Data1;

          Join

          Load Distinct

                    1 AS nr2,

                    nr2 as nr

          Resident Data2;

          Join

          Load Distinct

                    1 AS nr3,

                    nr3 as nr

          Resident Data3;

           

          Then, you can use the following expression to count your values, not matter about order of conditions:

           

          ='Set 1=' & Sum(nr1) & '

          Set 2=' & Sum(nr2)  & '

          Set 3=' & Sum(nr3)  & '

          Set2*Set1=' & Count({<nr2={1}, nr1={1}>} DISTINCT nr) & '

          Set2*Set3=' & Count({<nr2={1}, nr3={1}>} DISTINCT nr) & '

          Set1*Set3=' & Count({<nr1={1}, nr3={1}>} DISTINCT nr) & '

          Set1*Set2*Set3=' & Count({<nr1={1}, nr2={1}, nr3={1}>} DISTINCT nr)

           

           

          JG