4 Replies Latest reply on Sep 15, 2017 10:33 AM by sterling.ku

    Calculating date differences between multiple records

    sterling.ku

      Hi all,

       

      I am having trouble on calculating the most recent date when given multiple records and the difference between dates when keeping a field consistent.

       

      Here is sample data that I would work on:

      Audit DateCurrent StatusIncident IDPrevious Status
      8/10/17 12:00 AMAssigned111(null)
      8/10/17 2:00 PMAssigned111Assigned
      8/10/17 5:00 PMAssigned111Assigned
      8/12/17 12:00 AMIn Progress111Assigned
      8/13/17 12:00 AMPending111In Progress
      8/14/17 12:00 AMResolved111Pending
      8/15/17 2:00 AMResolved111Resolved
      8/15/17 12:00 AMAssigned222(null)
      8/16/17 12:00 AMIn Progress222Assigned
      8/17/17 12:00 AMPending222In Progress
      8/18/17 12:00 AMIn Progress222Pending
      8/19/17 12:00 AMPending222In Progress
      8/19/17 4:00 PMResolved222Pending
      8/19/17 12:00 AMAssigned333(null)
      8/20/17 12:00 AMIn Progress333Assigned
      8/21/17 12:00 AMPending333In Progress
      8/22/17 12:00 AMResolved333Pending

       

      #1. I want to be able to find the most recent Audit Date for each Incident ID's Status.  Expected result:

          

      Audit DateCurrent StatusIncident IDPrevious Status
      8/10/17 5:00 PMAssigned111Assigned
      8/12/17 12:00 AMIn Progress111Assigned
      8/13/17 12:00 AMPending111In Progress
      8/15/17 2:00 AMResolved111Resolved
      8/15/17 12:00 AMAssigned222(null)
      8/18/17 12:00 AMIn Progress222Pending
      8/19/17 12:00 AMPending222In Progress
      8/19/17 4:00 PMResolved222Pending
      8/19/17 12:00 AMAssigned333(null)
      8/20/17 12:00 AMIn Progress333Assigned
      8/21/17 12:00 AMPending333In Progress
      8/22/17 12:00 AMResolved333Pending

       

      Here's my formula:

      IF [Audit Date] = {FIXED [Incident ID1], [Current Status]: MAX([Audit Date])}

          THEN [Audit Date]

      END

       

      #2. I also would like to calculate the difference in time between specific statuses, while taking #1 into consideration.  I.e. time difference in minutes between Pending and Assigned.  I think I need to use the formula in #1 for this.

       

      Here's my formula:

      IF CONTAINS([Current Status], "Pending") AND DATEDIFF('minute',[???],[???]) <= 1440

        THEN 'Green'

      ELSEIF CONTAINS([Current Status], "Pending") AND DATEDIFF('minute',[???],[???]) > 1440

        THEN 'Red'

      END

       

      Any help would be greatly appreciated, thanks!