Calculation of overdue loans payable

[leonid_burlakov]

Good afternoon! I have problem with calculating overdue debts for each day. We sell goods on credit. Has a schedule of payments on the amount of payments and dates and the actual amounts and dates of customer payments. It is necessary to calculate the arrears and the date range. In the attachment example of a data set and required report. The task should be solved by the load script, the amount of data is very large. Please, help! Thank you!

[sunny_talwar]

Can you walk over the logic you are using there to create your report... it seems that sometimes you are picking the ScheduledPaymentDate as From Date and sometimes you are not. Also sometimes you are doing subtraction between CumulativeRegularAmountToPay and CumulativeRegularAmountPayed on the same row, but other times you do it on the different rows for OverdueAmount column.

[leonid_burlakov]

Hello Sunny!

Debt varies depending on the payments received and on the dates when these payments have come.

Therefore one record can turn into two intervals - a debt from the moment of delay on the schedule of payments, and a debt after payment if payment was not complete.

A set of rules is required. It is necessary to analyze both the next record and the previous.

I created a lot of flags to identify each case, but so far I have not achieved the result.

[sunny_talwar]

So what are those rules? I mean if you need help from us here, you would need to give us better direction

[leonid_burlakov]

Hello! Thank you for your interest in my question. I found a solution myself. If someone has a similar problem I can help.