3 Replies Latest reply on Jun 12, 2017 2:32 PM by Jeff Arledge

    Trying to find appropriate action date based on event date.

    Jeff Arledge

      Hi, this may be a little difficult for me to ask but here goes. I have an event date and then many action dates tied to that event. I am trying to determine the lowest response time for the event date. In other words, the action is in response to the event. I calculated the date diff and then used window_min to select the best value, also evaluated whether the event date was between the action response start and end date. The part I cannot figure out is the scenario in which there was no action response date AFTER the event date nor was the event date between the action start and end date. I'm having difficulty masking the data to get into a packaged workbook so in the mean time have attached an excel spreadsheet with an example of these records. I used a true/false calculated field to first identify event dates that were between the action start/end time or the response time was >0, then used the window_min to select the closest response time to the event date. This works great except filters out records that have no action date/time after the event date/time (row 32 in attached). Thanks for any help anyone may be able to provide!

       

      True/False Calculation: [EventDateTime]>=[ActionStartDate] and [EventDateTime]<=[ActionEndDate] or [ResponseTime]>0

      WindowMin Calculation: min([ResponseTime])=window_min(min([ResponseTime]))