How to use Distinct with sum in Set Analysis??

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!!

Re: How to use Distinct with sum in Set Analysis??

effinty2112 — Wed, 31 Aug 2016 09:48:56 GMT

Hi Roopali,

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

Cheers

Andrew

Re: How to use Distinct with sum in Set Analysis??

sunny_talwar — Wed, 31 Aug 2016 09:49:55 GMT

Why don't you count the category itself then

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

topic How to use Distinct with sum in Set Analysis?? in QlikView

How to use Distinct with sum in Set Analysis??

Re: How to use Distinct with sum in Set Analysis??

Re: How to use Distinct with sum in Set Analysis??