how to include only a specific row from duplicates?

[Anonymous]

I have the following requirement: Table1 is my trans table and table2 has status info on ProductIDs. I want to count only those productID which has only Expired status not Created. In table2, you can see all IDs have 2 statuses except for XYZ004 & XYZ006. How do i set up syntax on the front end so that it counts only 004 & 006. I have ----->

count(DISTINCT(if(Status='EXPIRED','ProductID'))) and it is not bringing the correct result.

Table1:

Table2:

My current syntax is showing this result. how to achieve right Result?

[rwunderlich]

Is the aggr() even required in this case? ProductID is already the chart dimension?

-Rob

[timanshu]

I actually taken ProductID and Status both as dimension. And also if for a product id having multiple records with status as expired, then it will count multiple times. but ideally it should count only 1 for single productid.

Also, same goes with the expression you suggested. If for a product id having multiple records with status as expired, then it will count multiple times.

I know this may not happen in scenario that John faced. So, both expressions are nice to apply.

[Anonymous]

hi Rob, Thanks again for the solution you provided. In my effort to understand your work, I have the following interpretation from your expressions. Any comments/corrections will encourage me.

1) count({<ProductID=E({<Status-={EXPIRED}>})>}Distinct ProductID)

Here, the Expn is telling to exclude all non-Expired ProdIDs & distinctly count the remainder ProdIDS.

2) count({<Status={EXPIRED}, ProductID={"=count(DISTINCT Status)=1"}>}Distinct ProductID)

here it should be understood that consider only those Records with Expired Status & within that, distinctly count those ProdIDs that have only one type of status (is this equivalent to the part of your expn: ProductID={"=count(DISTINCT Status)=1"}??

or :  consider only those Records with Expired Status & within that, distinctly count those ProdIDs that occur with only 1 type of status..?

Many Thanks.

[timanshu]

Yes. Exactly.

I request you to kindly mark the Question as answered. It will help others to easily determine the right answer.

[Anonymous]

Timanshu, Done

[rwunderlich]

2)count({<Status={EXPIRED}, ProductID={"=count(DISTINCT Status)=1"}>}Distinct ProductID)

The comma in the Set Expression should be read as "AND". Think of the set expression (everything between {}) as selections.

Select Status=EXPIRED AND count(of Statuses)=1, then count(DISTINCT ProductID)

-Rob

[Anonymous]

Thanks Rob