Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Count with 3 Value in 1 Dimension fulfill

Dear All,

Can anybody help for one case that i facing   

Case :

i want to count how many people that bought all PEN , BOOK and ERASER  (if they bought BOOK and ERASER, not counted)

and have to be distinct.

the correct answer should be = 2 --> A and D

DATENAMEITEMQTY
1-NovABOOK1
1-NovAPEN1
1-NovBERASER1
1-NovBBOOK1
1-NovCBOOK1
2-NovAERASER1
2-NovDBOOK1
2-NovDPEN1
2-NovDERASER1
2-NovEMAGAZINE1
2-NovEMAGAZINE1
2-NovDBOOK1

Thanks in advance

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Count with 3 Value in 1 Dimension fulfill

=COUNT({<NAME = p({<ITEM= {'PEN'}>})*p({<ITEM= {'BOOK'}>})*p({<ITEM= {'ERASER'}>}) >}DISTINCT NAME)

or

=COUNT({<NAME = {"=COUNT({<ITEM = {'PEN','BOOK','ERASER'}>}DISTINCT ITEM)=3"}>} DISTINCT NAME)

2 Replies
MVP
MVP

Re: Count with 3 Value in 1 Dimension fulfill

=COUNT({<NAME = p({<ITEM= {'PEN'}>})*p({<ITEM= {'BOOK'}>})*p({<ITEM= {'ERASER'}>}) >}DISTINCT NAME)

or

=COUNT({<NAME = {"=COUNT({<ITEM = {'PEN','BOOK','ERASER'}>}DISTINCT ITEM)=3"}>} DISTINCT NAME)

Not applicable

Re: Count with 3 Value in 1 Dimension fulfill

thanks a lot swuehl. the formula is correct and giving the right result.

can i know what is "p" stand for ?

Community Browser