Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
EMA
Contributor II
Contributor II

Count Distinct by Multiple Criteria with Max Date

 

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.

 
 

set.png

TIA 

Ed

Labels (1)
2 Solutions

Accepted Solutions
tresesco
MVP
MVP

Try:

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

View solution in original post

EMA
Contributor II
Contributor II
Author

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_#))

View solution in original post

3 Replies
tresesco
MVP
MVP

Try:

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

EMA
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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_#))