How to use SET Analysis for this

[Anonymous]

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.

[martinpohl]

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

see attached

Regards

[MK_QSL]

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)

[hic · Accepted solution]

An alternative to Aggr() is the following:

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

HIC

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

MRKachhiaIMP‌ ? any suggestions on that?

[MK_QSL]

Check enclosed file..