Count Distinct by Multiple Criteria with Max Date

[EMA]

I am hoping someone can provide a set analysis that allows me to count warranty coverage by coverage type and by distinct serial # with the latest coverage end date if there are multiple rows with the same serial #.

For serial # 1000101, the coverage is currently "Basic Coverage".  But, the customer has already signed for "Premium Coverage" that starts immediately after the current coverage ends.  For business purpose, I want to count this unit's coverage as "Premium Coverage" only. 

Below is example of data set.

TIA 

Ed

[tresesco · Accepted solution]

Try:

Count(DISTINCT If( [End Date]= Aggr(Max([End Date]), [Serial #]),[Serial #]))

[EMA]

Hi Tresesco.  I think it's very close.  How do I modify your set analysis to filter by Coverage Type, so I have one set analysis  for "Basic Coverage" and another for "Premium Coverage"?

[EMA · Accepted solution]

A colleague figured it out.

Count(DISTINCT If(End_Date= Aggr( Nodistinct Max(End_Date), Serial_#) and Coverage_Type = 'Basic'  ,Serial_#))

Count(DISTINCT If(End_Date= Aggr( Nodistinct Max(End_Date), Serial_#) and Coverage_Type = 'Premium'  ,Serial_#))