Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Count Exclude if any

Hi guys,

Got confused in some calculation.

Doc No.      Item         Qty     Customer

INV-01          A             2           ALPHA

INV-01          B             1            ALPHA

INV-02          B              2           DELTA

INV-03          A              1            OMEGA

i want to count how many customer that bought ITEM "B" only. so the correct answer should be "1"

can anybody help ?

1 Solution

Accepted Solutions

Re: Count Exclude if any

=SUM(Aggr(IF(Count({<Item = {'B'}>}DISTINCT Item)=1 and COUNT(DISTINCT Item)=1,1),Customer,[Doc No]))

Set Analysis

=COUNT({<[Doc No] = {"=Count({<Item = {'B'}>}Distinct Item)=1"}>*<[Doc No] = {"=Count(Distinct(Item))=1"}>}DISTINCT Customer)

7 Replies

Re: Count Exclude if any

=SUM(Aggr(IF(Count({<Item = {'B'}>}DISTINCT Item)=1 and COUNT(DISTINCT Item)=1,1),Customer,[Doc No]))

Set Analysis

=COUNT({<[Doc No] = {"=Count({<Item = {'B'}>}Distinct Item)=1"}>*<[Doc No] = {"=Count(Distinct(Item))=1"}>}DISTINCT Customer)

Re: Count Exclude if any

Try this expression in your chart:

Sum({$ <Item={'B'}>} If(Aggr(NODISTINCT Count(Item), Customer)=1,1,0))

let me know

antoniotiman
Honored Contributor III

Re: Count Exclude if any

=Count({<Customer={"=Only(Item)='B'"}>} DISTINCT Customer)

Not applicable

Re: Count Exclude if any

Thanks Manish, i'm using your Set Analysis and it worked

Not applicable

Re: Count Exclude if any

Thanks Alessandro, but the result was wrong in my file.

Not applicable

Re: Count Exclude if any

Thanks Antonio , but didn;t show the correct answer.

rgvavihs
Valued Contributor

Re: Count Exclude if any

=Count({<Item= {'B'}>}Customer)

Community Browser