Null dates in aging formula 1

[mmwdmr]

I am trying to set up accounts receivable aging parameters. Initially, I was using one field to calcultate the aging (racct.lastpaydate) however, if there has been no payment, this date is null. So, if this field is null, I have to look at the invoice created date and age off of that. I have tried several combinations of "ands" and "ors" in my aging formula but can't seem to nail it. Here is where I am with the formula:

if IsNull ({racct.lastpaydate}) and CurrentDate - {rheader.create_date} >= 121 then 5 else
if not IsNull ({racct.lastpaydate}) and CurrentDate - {racct.lastpaydate >= 121 then 5 else
if CurrentDate - {racct.lastpaydate} in [91 to 120] then 4 else
if CurrentDate - {racct.lastpaydate} in [61 to 90] then 3 else
if CurrentDate - {racct.lastpaydate} in [30 to 60 then 2 else
if CurrentDate - {racct.lastpaydate} < 30 then 1 else 0

The IsNull part of the formula for the 121 days is working (for both the lastpaydate or created_date) but obviously there are still invoice created dates that are not falling in the correct aging category when last paydate is null. Any suggestions are appreciated!

 

[rosemaryl]

Dear mmwdmr,

I think a better approach is the one below:

//begin formula

local datevar d1 := If Isnull({racct.lastpaydate}) 
                    then {rheader.create_date}
                    else {racct.lastpaydate};

local datevar d2 := currentdate;

numbervar age := datediff('d',d1,d2);

Select age

case  0 to 29  : 1
case 30 to 60  : 2
case 61 to 90  : 3
case 91 to 120 : 4
default        : 5

//end formula

I like case statements because they are easier to read.

The default item means, if all the case statements are false then default is true so return this value.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[mmwdmr]

Thanks, I'll give it a try!

 

[CR85user]

Nice approach Ro - Just want to add my thanks in as well for helping me learn a little more

J