Aging Formula

[Soleil525]

Hello All,
I'm new to CR and need your help. I am using CR 8 reporting off Visual Fox Pro database. In the claim table I have a status column that contain various status (completed, pending, canceled, etc…). What I like to do is calculate the claim total base on the status and the claim date aging. For example

Completed Pending Canceled
Over 30 days $1500.00 $3000.00 $500.00
Over 60 days $7000.00 $9000.00 $1000.00
Over 90 days $12000.00 $10500.00 $1500.00
Over 120 days
Over 150 days
Over 180 days…

What I have now is this:
@completed
If {claim.status} = ‘completed’ and {claim.date} < (currendate – 30) then {claim.total}
Else
0

It doesn’t seem right??? Can someone please help me. Your help is greatly appreciated.
TIA

 

[dgillz]

Your approach is what I would do but i would take advantage of the built in functions:

Aged0to30Days
Aged31to60Days
Aged61to90Days
Over90Days

Let me know if you have any questions. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[Soleil525]

Thank you for a quick respond. So my formula should look something like this:
@completed
If {claim.status} = ‘completed’ and {claim.date} In Aged31to60Days will give the claim total of all claims that is over 30 days but not older than 60 days right?

What about the break down of claims that aged between 90 days –120 days, 120 day – 150 days and 150 days – 180 days. Can you point me into the right direction. Again your help is greatly appreciated.

Brian

 

[dgillz]

Yes your formula should look like that.

For the other groups, you will have to write the code out similar to your initial post as there is no built in function for beyond 90 days except the over90days functoin which is all inclusive.


I have seen a Next30days function. You might play with that a bit. I have never used it. If it comes in handy please post to the board.
Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[Soleil525]

Thank you for your input. I'll try it and post the result.