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

    Distinct count of customer based on latest date

    Wong Soon

      Hi, all

       

      I'm here with some issue in set analysis.

      Imagine I want the following table.

       

      CUST_IDTXN_DT_IDSTATUSRemark
      A20180802NExcluded
      A20180731Y
      B20180630YIncluded
      C20180801Y
      C20180730N
      C20180804NExcluded
      D20180803YIncluded
      D20180802N
      D20180801N
      D20180731N

         

      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.