Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Past Due items

Status
Not open for further replies.

veles

Technical User
Sep 1, 2006
57
CA
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.
 
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:

Code:
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
 
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
 
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
 
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
 
ex:

else if ({S_EVT.TODO_DUE_DT}) in [currentdate-30 to currentdate-10] then "Past by up to 30 Days
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top