Creating Customer Month End balances from transactions
I am working on an application that has customer (debtor) transactions, including invoices and payments. I can easily calculate the current balance outstanding for a customer by summing all the transactions. However, what I want to do is know what the balance was at the end of each month for each customer. This will allow me to report on the trend of balances over time.
My thinking is to build a data table in the script rather than using calculations within reporting objects in order to speed up usability. I wondered about looping through each customer and then for each month end calculating the sum of all transactions to the month end date. My hope was to create a table that would include 3 fields - customer code, month end date, balance. I haven't actually worked out how to do this and I think there is probably a smarter way to do this.
Are there any other suggestions for how I could do this?
