1 Reply Latest reply: Jan 22, 2015 2:53 PM by Marco Raymundo RSS

    Multiple Records making 1 Overdue.....

    Leo Catt

      Hi all,

      I have an interesting problem I hope someone can help me with.

       

      We track records that are broken up into smaller records that we track OnTime and Overdue.

       

      so the basic formula is:

       

      if the Record is in Status1 AND the status_due IS LESS THAN OR EQUAL TO Today THEN "OnTime" ELSE "Overdue"

       

      so ultimately the matrix looks something like this:

      Status 1Status 2Status 3Status 4Record Status
      Record 1Status 1Overdue On TimeOn TimeOn TimeOverdue
      Record 2Status 2Overdue On TimeOn TimeOn TimeOn Time
      Record 3Status 3On TimeOverdueOn TimeOn TimeOn Time
      Record 4Status 4OverdueOn TimeOverdueOverdueOverdue

       

      So I have set up the code to this for the load:

       

      if(Today() > [Calculated Eval Due], 'Overdue', if(Today() <= [Calculated Eval Due], 'OnTime', 'Null')) as EVAL_ONTIME,
      if(Today() > [Calculated Cont Due], 'Overdue', if(Today() <= [Calculated Cont Due], 'OnTime', 'Null')) as CONTAINMENT_ONTIME,
      if(Today() > [CAPAPlanDue], 'Overdue', if(Today() <= [CAPAPlanDue], 'OnTime', 'Null')) as CAPAPLAN_ONTIME,
      if(Today() > [RootCause Due], 'Overdue', if(Today() <= [RootCause Due], 'OnTime', 'Null')) as ROOTCAUSE_ONTIME,
      if(Today() > [VVOEDue], 'Overdue', if(Today() <= [VVOEDue], 'OnTime', 'Null')) as VVOE_ONTIME,
      if(Today() > [EffCkDue], 'Overdue', if(Today() <= [EffCkDue], 'OnTime', 'Null')) as EFFCK_ONTIME;

       

      Here is the issue,

      Once a record moves to the next status, the previous Ontime/Overdue status doesnt matter.  Since eventually all records will be overdue,  I need to find a way to not count the Parts that dont count which are 2 fold:

       

      1. the parts that have been past

      2. the parts that are not yet in play

       

       

      Has anyone run into this problem before?  did you solve it? how?

       

      Any help I can get would be appreciated.