3 Replies Latest reply: Aug 21, 2018 1:13 AM by Max Pain

# Distinct count of customer based on latest date

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?

• ###### Re: Distinct count of customer based on latest date

Can you try this

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

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

)

• ###### Re: Distinct count of customer based on latest date

Hi Max,

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

• ###### Re: Distinct count of customer based on latest date

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