Set analysis intersection

[juleshartley]

HI All,

I'm trying to set the selections in one field 'SchemeNo' to the values of 'PeerSchemeNo' that are the intersection of 2 simple sets. 

I've tried this, but it seems to only return 0 (there is definitely some overlap)

count(DISTINCT

{1
<[SchemeNo]=P({<PeerGroupName={"$(vPG1)"}>}PeerSchemeNo)>
*
<[SchemeNo]=P({<PeerGroupName={"$(vPG2)"}>}PeerSchemeNo)>
}
SchemeNo
)

If I set it all to use 'PeerSchemeNo' instead of SchemeNo, then I get the correct number of intersecting schemes. But it is not that field that I want to make affect. Also, if I just use one of the 'P' sets above then it does give me the expected answer, so they seem to be working in isolation.

Thanks,

Julian

[sunny_talwar]

Without a sample, this is more going to be a guess work, but try this

Count(DISTINCT {1<[SchemeNo] = P({<PeerGroupName = {"$(vPG1)"}>} PeerSchemeNo) * P({<PeerGroupName = {"$(vPG2)"}>} PeerSchemeNo)>} SchemeNo)

[marcus_sommer]

Try it with:

count(DISTINCT

{1
<[PeerGroupName]=P({<PeerGroupName={"$(vPG1)"}>}PeerSchemeNo)>
*
<[PeerGroupName]=P({<PeerGroupName={"$(vPG2)"}>}PeerSchemeNo)>
}
SchemeNo
)

- Marcus

[juleshartley]

Thanks both - I'm not sure why it didn't work but I've ended up using a concat to build a string to feed into the set analysis, which seems to work ok

[juleshartley · Accepted solution]

SchemeNo={

$(

=concat(DISTINCT{<[PeerSchemeNo]=P({<PeerGroupName={"$(vPG_First)"}>}PeerSchemeNo)>

*

<[PeerSchemeNo]=P({<PeerGroupName={"$(vPG_Last)"}>}PeerSchemeNo)>}PeerSchemeNo,', ')

)

}