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 1 | Status 2 | Status 3 | Status 4 | | Record Status |
| Record 1 | Status 1 | Overdue | On Time | On Time | On Time | | Overdue |
| Record 2 | Status 2 | Overdue | On Time | On Time | On Time | | On Time |
| Record 3 | Status 3 | On Time | Overdue | On Time | On Time | | On Time |
| Record 4 | Status 4 | Overdue | On Time | Overdue | Overdue | | Overdue |
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.
