2 Replies Latest reply: Feb 11, 2016 9:26 AM by Zach Taylor RSS

    Count if claims between dates

      Hello community,

       

      I'm attempting to isolate points in time in order to compare counts as of a month end date.

       

      The expression below works:

       

      Count(If(Date#(01/07/2016)>=[DMClaimHistory.SCD_Valid_From_Date] and

      Date#(01/07/2016)<=[DMClaimHistory.SCD_Valid_To_Date] and

      Date#(01/07/2016)>=DMClaimHistory.Initial_Open_Date and

      ([DMClaimHistory.Claim_Status]='O' or [DMClaimHistory.Claim_Status]='R') ,

      [Claim_Number],))

       

      This, however, does not work:

       

      Count(If(Date#(01/31/2016)>=[DMClaimHistory.SCD_Valid_From_Date] and

      Date#(01/31/2016)<=[DMClaimHistory.SCD_Valid_To_Date] and

      Date#(01/31/2016)>=DMClaimHistory.Initial_Open_Date and

      ([DMClaimHistory.Claim_Status]='O' or [DMClaimHistory.Claim_Status]='R') ,

      [Claim_Number],))

       

      Whenever I change the day (MM/DD/YYYY) to a double digit number (referring to "07" or "05" as "single digit" and "10", "11", "12"..., "31" as double digit) the expression returns zeros.

       

      Any assistance given is very appreciated.