How to use Distinct with sum in Set Analysis??

[Anonymous]

Hi,

I have a table as below:

Category	Reason	Customer	Product Category	Product	Counter
A	Buy	1	Iron	Steam Iron	1
A	Buy	1	Iron	Standard Iron	1
B	Buy	1	Fryer	Air Fryer	1
A	Buy	2	Iron	Standard Iron	1
B	Ask	2	Cooker	Rice Cooker	1
C	Buy	2	Fryer	Air Fryer	1

I am currently using the set analysis to Calculate sum of Counter -

sum({$<Category = {"A", "B"}, Reason = {"Buy"} >} Counter)

It gives me a sum of Counter if Category is A or B and Reason is Buy. However, with this case, I am getting sum as 3 for customer 1 and 1 for Customer 2.

I want to add a condition where it only sums counter for each customer where Product Category is Unique.

So I want a sum of counter = 2 for Customer 1 as it is looking at the same product category in two instances.

I hope I made sense in explaining.

Any help is much appreciated.

Thank You in Advance!!

[effinty2112 · Accepted solution]

Hi Roopali,

Try :  Count({$<Category = {'A', 'B'}, Reason = {'Buy'} >}DISTINCT [Product Category]

Cheers

Andrew

[sunny_talwar]

Why don't you count the category itself then

Count(DISTINCT {$<Category = {"A", "B"}, Reason = {"Buy"} >} Category)