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

# 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

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:

1 AS nr1,

nr1 as nr

Resident Data1;

Join

1 AS nr2,

nr2 as nr

Resident Data2;

Join

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