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

# Multiple Records making 1 Overdue.....

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.

• ###### Re: Multiple Records making 1 Overdue.....

Hi!

You can post a qvw example?