topic Re: How to use SET Analysis for this in QlikView

How to use SET Analysis for this

Anonymous — Wed, 24 Jun 2015 19:18:25 GMT

I want to pull productIDs only when it has Status Y and N, both. so in my straight table, it should only pull ProductID001.

I am using count(distinct(if(Staus ='Y' and Status ='N', ProductID))) but it is not working.

Re: How to use SET Analysis for this

martinpohl — Wed, 24 Jun 2015 19:35:12 GMT

your if-statement could not give back a true because no dataset has both values in one line

see attached

Regards

Re: How to use SET Analysis for this

MK_QSL — Wed, 24 Jun 2015 19:38:47 GMT

If you want number of ProductID having Y and N status, use below in Text Box

=SUM(Aggr(IF(COUNT(DISTINCT Status)>1,1),ProductID))

If you want to use ProductID having Y and N both status in Chart or Tables, use below as a Calculated Dimension and

Tick Suppress When value is null

=Aggr(IF(COUNT(DISTINCT Status)>1,ProductID),ProductID)

Re: How to use SET Analysis for this

hic — Wed, 24 Jun 2015 19:42:48 GMT

An alternative to Aggr() is the following:

Count({$<ProductID=P({$<Status={Y}>}ProductID)*P({$<Status={N}>}ProductID)>} distinct ProductID)

HIC

Re: How to use SET Analysis for this

Anonymous — Wed, 24 Jun 2015 21:05:38 GMT

Manish Thank you. I used =Aggr(IF(COUNT(DISTINCT Status)>1,ProductID),ProductID) as calculated dimension & ticked suppress null value. So it is letting me see both status N and Y. now how do I include the count at red circle in the image that will count no of ProductIDs. as of now I have used ClassID as an expn. If I use =SUM(Aggr(IF(COUNT(DISTINCT Status)>1,1),ProductID)) in the expression, it will count as I desire but that wont bring status Y and N though. So how do I achieve it.

Re: How to use SET Analysis for this

Anonymous — Wed, 24 Jun 2015 21:06:06 GMT

HIC Thank you very much. That was a perfect solution.

Re: How to use SET Analysis for this

Anonymous — Wed, 24 Jun 2015 23:23:24 GMT

MRKachhiaIMP‌ ? any suggestions on that?

Re: How to use SET Analysis for this

MK_QSL — Thu, 25 Jun 2015 06:21:01 GMT

Check enclosed file..