5 Replies Latest reply on Feb 28, 2016 7:22 PM by himhim chan

    Count date difference between latest record and the nearest record

    himhim chan

      Hi all,

       

      I want to find out the date difference between latest record and the nearest record.

       

      However, previous record in the same month will be excluded.

       

      I have the data below:

                                                           

                                                           

      A18-Jan-16
      8-Jan-16
      15-Dec-15
      23-Oct-15
      15-Oct-15
      14-Oct-15
      B20-Jan-16
      19-Jan-16
      12-Jan-16
      5-Jan-16
      31-Dec-15
      24-Dec-15
      10-Dec-15
      24-Nov-15

       

      And I would like to have this result:

      A: 18-JAN-16   -    15-DEC-15  = 34  (8-JAN-15 will be excluded)

      B: 20-JAN-16   -    31-DEC-15  = 20  (Ignore All JAN data)

       

      I tried Previous_value and lookup of MAX(date) but still not work.

      Does anyone know if there is a way to do it?