Set analysis problem: implicit field value not being accounted

[Liz]

Hi everyone!

It's my second question on the community!

So, I'm developing a few calculated fields, and this time I want a different kind of set analysis:

There's a list of buyers and sellers and qty bought. In the list bellow there are 10 distinct buyers, but only 6 are "unique" buyers (those that bought from only one seller - Seller1, in this example)

What I want is: given there's 10 - 6 = 4 buyers that bought from Seller1 and other buyers, how many of these also bought from Seller2?

When I tried use Set Analysis to consider only the number of buyers that bought from Seller1 that also bought from another set of Sellers...:

Count({<Seller=P({<Seller={'Seller2'}>} Seller)>} Aggr(DISTINCT Seller, Buyer))

...it only shows the unique buyers of Seller2 on the Seller2 line (on a pivot table) and shows zeros on the other sellers.

Original table:

Buyer	Seller	TotalQty
21339965	Seller3	20
21339965	Seller1	2
16631233	Seller1	291
06967098	Seller2	185
06967098	Seller3	114
06967098	Seller1	17
84858307	Seller2	46
84858307	Seller1	80
07272825	Seller2	345
07272825	Seller1	460
08050237	Seller1	5
10462976	Seller1	25
62115431	Seller1	9
03728965	Seller1	3
07147692	Seller1	385

[sunny_talwar · Accepted solution]

Try this

Count(DISTINCT {<Buyer = {"=Count(DISTINCT Seller) > 1"}, Seller = {'Seller2'}>} Buyer)

[Liz]

When I first saw my first post solution you replied, I realized that this is the solution for all my problems!
On the seller subset part, I modified to retrieve all the possible sellers based on another field with the P() function, and it worked perfectly on my data!
Thank you again!
Let me ask you something: where (on the documentation) is this topic (the way you used an expression inside quotation marks to represent a condition) discussed?
So I can learn more!

[sunny_talwar]

This post from HIC talks about the search string briefly

A Primer on Set Analysis