Need help on backward calculation

[litane]

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 number	Client	Lettering	Invoice date	Due date	Invoice amount
FC-L3219000379	SCL0037	AO	11/02/2019	25/03/2019	15 987
FC-L3219000380	SCL0037	AO	11/02/2019	09/03/2019	1 620
FC-L3219000381	SCL0037	AO	11/02/2019	07/03/2019	15 987

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

Payment date	Client	Lettering	Paid Amount
06/07/2020	SCL0037	AO	5850
12/03/2020	SCL0037	AO	7858
15/01/2020	SCL0037	AO	6000
03/12/2019	SCL0037	AO	6000
03/05/2019	SCL0037	AO	7000

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 number	Client	Lettering	Invoice date	Due Date	Invoice amount	Paid Amount	Balance	Payment date
FC-L3219000379	SCL0037	AO	11/02/2019	25/03/2019	15 987	0	886	null
FC-L3219000379	SCL0037	AO	11/02/2019	25/03/2019	15 987	5850	5850	06/07/2020
FC-L3219000379	SCL0037	AO	11/02/2019	25/03/2019	15 987	7858	7858	12/03/2020
FC-L3219000379	SCL0037	AO	11/02/2019	25/03/2019	15 987	6000	1 393	15/01/2020
FC-L3219000380	SCL0037	AO	11/02/2019	09/03/2019	1 620	6000	1 620	15/01/2020
FC-L3219000381	SCL0037	AO	11/02/2019	07/03/2019	15 987	6000	2 987	15/01/2020
FC-L3219000381	SCL0037	AO	11/02/2019	07/03/2019	15 987	6000	6000	03/12/2019
FC-L3219000381	SCL0037	AO	11/02/2019	07/03/2019	15 987	7000	7000	03/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 solution]

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!

[litane]

Hi @jasperlch 

This works perfectly !

thank you so much