Sum total from third field after aggr(distinct Field1, Field2)

[Liz]

Hi everyone!

I'm developing a few calculated fields and I'm not able to solve this problem:

There's a list of sellers and buyers and qty bought.

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

First of all, I was able to count distinct and unique buyers (those that buy from only one seller) with a simple Count(Aggr(DISTINCT FieldSeller, FieldBuyers).

There are 10 distinct buyers but it results that only Seller1 has 6 unique buyers (buyers that did not buy from any other seller).

But now I want to sum all the totals bought only by the unique buyers. It means that I want to sum all qty bought only by these 6 unique buyers.

[sunny_talwar · Accepted solution]

Try these

For Count
Count(DISTINCT {<Buyer = {"=Count(DISTINCT Seller) = 1"}>} Buyer)

For Sum
Sum({<Buyer = {"=Count(DISTINCT Seller) = 1"}>} TotalQty)

[Liz]

Wow! That was fast!
Thank you very much!
It worked perfectly and I learned a new tool I wasn't able to find on the documentation!

[sunny_talwar]

Awesome, I am glad I was able to help 🙂