Past Due items

[veles]

I need help with a formula calculating past due dates based on a field

if ({S_EVT.DUE_DT}) < currentdate-60 then "Past Due By 60 Days"
else if ({S_EVT.DUE_DT}) < currentdate-90 then "Past Due By 90 Days"

The first part is working good, but the 90 day part isn't. I beleive it has to determine a range between 61 and 90, but I am kind of stuck there.

Any help is appreciated.

 

[pmax9999]

The problem is wit the order of processing - even if an account is more than 90 days overdue it is picked up by the 1st part of the If statement. Reverse the orde as follows:

If 	({S_EVT.DUE_DT}) < currentdate-90 
Then 	"Past Due By 90 Days"
Else
If 	({S_EVT.DUE_DT}) < currentdate-60 
Then 	"Past Due By 60 Days"

Cheers
Pete

 

[veles]

Hi Pete,

Thanks for your reply

I did use your example and expanded it

if isnull ({S_EVT.TODO_DUE_DT}) then "No Due Date"
else if ({S_EVT.TODO_DUE_DT}) > currentdate then "Future"
else if ({S_EVT.TODO_DUE_DT}) < currentdate-90 then "Past Due By 90 Days"
else if ({S_EVT.TODO_DUE_DT}) < currentdate-60 then "Past Due By 60 Days"
else if ({S_EVT.TODO_DUE_DT}) < currentdate-30 then "Past Due By 30 Days"
else if ({S_EVT.TODO_DUE_DT}) < currentdate-10 then "Past Due By 10 Days"
Else "Check Formula"

I am getting 3 records with check formula. Today's date is 21Mar14, and the records dates are 12Mar14, 14Mar14 and 20Mar14.

I am trying to get records past due by up to 10, 30, 60 and 90 days, and include future and no due date records.

Thanks

 

[pmax9999]

That's because those 3 records are overdue by less than 10 days which is not covered by your formula. Amend the else "Check Formula" line to else "Past Due by less the 10 days".

Cheers
Pete

 

[veles]

Thanks Pete,

Now I am wondering if the formula covers what I was trying to acheive. I was under the impresion that using

if ({S_EVT.TODO_DUE_DT}) < currentdate-10 then "Past Due By 10 Days"

will cover any records with a due date past up to 10 days.

I am thinking that I need a range in the formula, not just -10, -30....

With the same example as above I need the formula to cover from

1 to 10 = past by up to 10 days,
11 to 30 = past by up to 30 days,
31 to 90 = past by up to 90 days,

I might need to change the string "Past Due By X Days"

Am I right here and if so can you please help updating the formula with ranges.

Thanks

 

[AndyMc]

ex:

else if ({S_EVT.TODO_DUE_DT}) in [currentdate-30 to currentdate-10] then "Past by up to 30 Days

 

[pmax9999]

You don't need to use a range.

Using the earlier approach will work, you just need to make sure the output from the If-Then-Else is clear. You could do this as follows:

if isnull ({S_EVT.TODO_DUE_DT}) then "No Due Date"
else if ({S_EVT.TODO_DUE_DT}) >= currentdate then "Future"
else if ({S_EVT.TODO_DUE_DT}) < currentdate-90 then "Past Due By 90+ Days"
else if ({S_EVT.TODO_DUE_DT}) < currentdate-60 then "Past Due By 60+ Days"
else if ({S_EVT.TODO_DUE_DT}) < currentdate-30 then "Past Due By 30+ Days"
else if ({S_EVT.TODO_DUE_DT}) < currentdate-10 then "Past Due By 10+ Days"
else "Past Due by less the 10 days"

Pete