30 day, 60 day, 90 day, subtract from Order Amount

  • Thread starter Thread starter Dave Elliott
  • Start date Start date
D

Dave Elliott

On my Report I have these formulas below. The customer gets charged 2% over
30, 3% over 60 and 4% over 90 days.
The date used is Order Date from orders table. I need now to calculate (Add)
whatever amount is 0-30 days to the total Order Amount, or if the
amount is 31-60 then add this amount or if the amount is 61-90 then add this
amount. Lost as to how to do this.

Thanks,

Dave



=Sum([Order Amount])
=Sum([0-30])
=Sum([0-60])
=Sum([0-90])

Grand Total =Sum([Order Amount])
 
This is likely a value you need to store in your database. If, for example,
I owe you $100 and go 30 days overdue, then I'll owe you $102. Let's say I
then pay you the original $100 and you record that in your db ... if you
haven't stored the amount of interest owed (the $2.00), then there's no way
for the program to notify you that I still owe it.

We call this "aging" although there are many different names for this.
Basically, you step through all open orders, determining (a) how old it is,
(b) how much is owed from the original balance (here, you can't charge
interest on anything else). You then calculate the amount of interest owed
and store this in a table, which includes the ID of the parent record (the
"order" record). Then, your report would be based on (a) your "orders" table
and (b) your "interest" table (along with any other tables required for your
report). This can be performed at a set time each month, daily, etc etc
based on the needs of your business or client.
 
Back
Top