how to solve this with Set Analysis and If statement

[Anonymous]

count those ProductIDs where status is created and distinct count of TransactionID =2. so in my below eg, I should get only XYZ002 with

TransID 10002 & 10003. I tried

count({<Status={CREATED}, ProductID={'=count(DISTINCT TransactionID)=2'}>}Distinct

ProductID)  but it is not working

[kaushiknsolanki]

Try this,

=Count({<Status = {"Created"},ProductId = {"=count({<Status = {'Created'}>}Distinct TrxID)=2"}>}Distinct ProductId)

Regards,

Kaushik Solanki

[swuehl · Accepted solution]

Maybe you are looking for

count({< ProductID={"=count({<Status={CREATED} >} DISTINCT TransactionID)=2"}>} Distinct

ProductID)

[petter]

How to solve it is a bit dependent on how you want to display your result - which you haven't told us.

I assume you want the result in a Straight Table and then it is quite simple to display the ProductID which

match your criteria like this:

Add one single dimension which is ProductID and one single expression which is

Count( { < Status = { 'CREATED' } > } TransactionID ) = 2

Then you will have a two column straight table with a single row - XYZ002.

If you want a list of ProductIDs in a text box you could use this expression:

=Concat( Aggr( If( $(A1)  , ProductID ) , ProductID ) , Chr(10) )

[Kushal_Chawda]

try,

Count({<ProductId = {"=count({<Status = {'CREATED'}>}Distinct TrasactionID)=2"}>}Distinct ProductId)

[maxgro]

count({<Status={CREATED}, ProductID={"=count({$ <Status={CREATED}>} DISTINCT TransactionID)=2"}>}Distinct ProductID)

[Anonymous]

Thanks everyone. Special Thanks goes to sweuhl for being the first responder to be correct.