Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
roopalid
New Contributor III

How to use Distinct with sum in Set Analysis??

Hi,

I have a table as below:

    

CategoryReasonCustomerProduct CategoryProductCounter
ABuy1IronSteam Iron1
ABuy1IronStandard Iron1
BBuy1FryerAir Fryer1
ABuy2IronStandard Iron1
BAsk2CookerRice Cooker1
CBuy2FryerAir Fryer1

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

1 Solution

Accepted Solutions
effinty2112
Honored Contributor

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

Hi Roopali,

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

Cheers

Andrew

2 Replies
effinty2112
Honored Contributor

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

Hi Roopali,

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

Cheers

Andrew

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

Why don't you count the category itself then

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