topic Re: Distinct count of customer based on latest date in App Development

Distinct count of customer based on latest date

Anonymous — Wed, 22 Dec 2021 15:42:54 GMT

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.

Re: Distinct count of customer based on latest date

Anonymous — Mon, 20 Aug 2018 18:43:13 GMT

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

Anonymous — Tue, 21 Aug 2018 04:12:02 GMT

Hi Max,

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