0 Replies Latest reply: Apr 25, 2017 7:33 AM by Rovika Poole RSS

    How to get the distinct count of a calculation

    Rovika Poole

      Hi all,

       

      Please help! I need to get the distinct count of the CIB_Cust _N (Customer Number) from the result of this sum.

      The issue is that the customer number is double counted in the result of the below 3 buckets. The sum of the below 3 buckets is approx. 4000 and the distinct is around 3700. 

       

      =


      //bucket 1
      count(distinct(if(
      ONE_TO_MANY = '1'
      and Merge <> 'No'
      and CIF_Merge_Status <> 'Successfully Merged'
      , CIB_CUST_N)))
        +


      //bucket 2
        count(distinct(if(left(CREATE_DATE,4) = '2017' and ONE_TO_MANY = '1'  and NEW_DUPLICATE = '1', CIB_CUST_N)))
        +
       
      //bucket 3
      count(distinct(if(
      Merge = ''
      and ONE_TO_MANY = '1'
      , CIB_CUST_N)))