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') ,



      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') ,



      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.