0 Replies Latest reply on Jun 21, 2013 9:27 AM by Eric Moore

    Date Metrics

    Eric Moore

      In my dataset, I have dates in the rows that represent steps within a process.

       

      I'll give example. 

       

      Issue

      IssueIDIncoming DateAnaylsis DateResolution Date
      16/1/20136/10/20136/15/2013
      26/1/2013null6/17/2013
      3null6/10/20136/15/2013
      46/1/2013nullnull

       

      The problem I frequently have is missing dates within these fields.  One thought of these dates if there is a null, then current date should be used.

       

       

      I create 2 calculated fields for each time cycle:

       

      cycle 1

      datediff("day",iif(date([IncomingDate]),[IncomingDate],[AnalysisDate]), iif(isdate(STR([AnalysisDate])),[AnalysisDate],Now()))

       

      cycle 2

      sames as cycle 1, except the start date is Analysis Date and the End Date is Resolution Date

       

      cycle 3 is the same as 1, except the first date (Incoming Date) is used for start date and Resolution Date is used.

       

      The idea is use an AVG aggregate to provide metrics on the delta times between the various dates.

       

      I then tried a second mechanism to throw out missing dates so I could deal with data that was correctly entered or captured. 

       

      IF (ISDATE(STR([Incoming Date])) AND ISDATE(STR([Analysis Date]))) then

      datediff("day",[Incoming Date],[Analysis Date])

      END

       

      I began seeing negative dates with the second approach, event though I was trying to filter out bad records by verifying my dates were correct.

       

       

      That led to why is the date negative which can only mean a date was entered in for the 2nd or 3rd date fields before the initial date.

       

      I reviewed a few date related issues here but wanted to see if there is another idea on handling date processes like this.  I suspect this is common type of operation to collect process that occur.

       

      Some problems are if you have a null, you probably want to use the next date process to prevent the application of todays date.

       

      Let me illustrate:

       

      Incoming Date:  6/1/2013

       

      Anaylsis Date:  not provided

       

      Resolution Date: 6/15/2013.

       

      Given the Analysis Date is missing, I'd like to use the Resolution Date or possibly use the Incoming date.  I could determine later what business rule I want to use, but the main issue is some ideas on handling date operations within processes that essentially mark some

      state of the issue being reported.