Need help on backward calculation

Question

Dear all,

I'm building a DSO aging balance for my client and need your help regarding the following issue

Here is a sample of two tables :

Table 1 : i have client invoices with dates, terms and amounts

Invoice numberClientLetteringInvoice dateDue dateInvoice amountFC-L3219000379SCL0037AO11/02/201925/03/201915 987FC-L3219000380SCL0037AO11/02/201909/03/20191 620FC-L3219000381SCL0037AO11/02/201907/03/201915 987

Table 2 : i have the payments received on these invoices with payment date and the amount received for each date

Payment dateClientLetteringPaid Amount06/07/2020SCL0037AO585012/03/2020SCL0037AO785815/01/2020SCL0037AO600003/12/2019SCL0037AO600003/05/2019SCL0037AO7000

I want to allocated payment dates to each invoice, and if total paid doesn't match total invoice, this means that part of the invoice has not yet been paid, and in this case i keep the payment date empty for the remaining invoice amount, and since we don't know which payment goes to which invoice, i created the following rule : we allocate payments starting from the oldest invoice due date and do backward calculation until we get the remaining balance to be paid, or get nothing if the invoices are fully paid

He is the final output i would like to get on Alteryx :

Invoice numberClientLetteringInvoice dateDue DateInvoice amountPaid AmountBalancePayment dateFC-L3219000379SCL0037AO11/02/201925/03/201915 9870886nullFC-L3219000379SCL0037AO11/02/201925/03/201915 9875850585006/07/2020FC-L3219000379SCL0037AO11/02/201925/03/201915 9877858785812/03/2020FC-L3219000379SCL0037AO11/02/201925/03/201915 98760001 39315/01/2020FC-L3219000380SCL0037AO11/02/201909/03/20191 62060001 62015/01/2020FC-L3219000381SCL0037AO11/02/201907/03/201915 98760002 98715/01/2020FC-L3219000381SCL0037AO11/02/201907/03/201915 9876000600003/12/2019FC-L3219000381SCL0037AO11/02/201907/03/201915 9877000700003/05/2019

Does anyone have any idea on how to do it ?

Please note that this calculation should be applied on a large number of invoices and clients

Many thanks

jasperlch · Accepted Answer

Hi @litane,

This is quite a challenge. I have come up with a solution but it is a little bit complicated:

Basically I have created an iterative macro which will work on one payment only for each combination of Client and Lettering in each iteration. Inside each iteration, the macro will first sort the invoice amount in ascending order of [Due Date] and then calculate the [Running remaining paid amount] and [Invoice amt yet to be paid after this payment] by using Multi-Row Formula. After each iteration, all the 'yet-to-be-paid' invoices will then be used as the input invoice data for the next round of iteration.

Workflow:

Macro:

Hope this helps!