Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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