Distinct count of customer based on latest date

[Anonymous]

Hi, all

I'm here with some issue in set analysis.

Imagine I want the following table.

CUST_ID	TXN_DT_ID	STATUS	Remark
A	20180802	N	Excluded
A	20180731	Y
B	20180630	Y	Included
C	20180801	Y
C	20180730	N
C	20180804	N	Excluded
D	20180803	Y	Included
D	20180802	N
D	20180801	N
D	20180731	N

To calculate the distinct count of customer with Status='Y' based on the latest date of each customer only. Based on the data above, my expected result should be 2.  

My set analysis, which is wrong somewhere :

count(Aggr(max(If(TXN_DT_ID = FirstSortedValue(TOTAL <CUST_ID> TXN_DT_ID), if(STATUS='Y',CUST_ID))), CUST_ID))

Anyone can help on the set analysis?

Thanks in advance.

[Anonymous]

Can you try this

sum({< STATUS={'Y'} >}

     if(TXN_DT_ID = Aggr(Max({< STATUS= >} TOTAL <CUST_ID> TXN_DT_ID ), CUST_ID), 1, 0)

)

[Anonymous]

Hi Max,

The value aggregated seems if there're more columns then will be incorrect.

[Anonymous]

H-m, not quite clear but I have  this result