Calculating Balance Due

[wcilliers]

Hi All,

I have a rather unique requirement (I Think) where I need to calculate the remaining amount due for a customer.
I have  a table which records each payment due by date. And then a separate table that only contains the total amount paid.

There is no link between the due date and payment date. The amount paid needs to be allocated against the earliest payment due amount until there is nothing left to allocate and then the remaining due will be displayed.

Here is an example:

TABLE1:

ClientAcc	Due Date	Amount Due
		10,559.28
2089902	30/04/2023	1,759.88
2089902	30/03/2023	1,759.88
2089902	28/02/2023	1,759.88
2089902	30/01/2023	1,759.88
2089902	30/12/2022	1,759.88
2089902	30/11/2022	1,759.88

TABLE2:

ClientAcc	Paid
2089902	5,379.64

What I require is the below result. (This was done in Excel)

REQUIRED RESULT:

ClientAcc	Due Date	Amount Due	Amount Paid	Amount Due
		10,559.28	5,379.64	5,179.64
2089902	30/04/2023	1,759.88	0.00	1,759.88
2089902	30/03/2023	1,759.88	0.00	1,759.88
2089902	28/02/2023	1,759.88	100.00	1,659.88
2089902	30/01/2023	1,759.88	1,759.88	0.00
2089902	30/12/2022	1,759.88	1,759.88	0.00
2089902	30/11/2022	1,759.88	1,759.88	0.00

Attached is a QlikView sample with the data.

Any help would be greatly appreciated.

Thanks,

Wynand

[stevedark]

Hi @wcilliers 

This doesn't sound that unique!

I think the safest way to deal with this is in the load script, something like:

tmpAmountsDue:
LOAD
	ClientAcc,
	[Due Date],
	[Amount Due]
FROM [your data source];

LEFT JOIN (tmpAmountsDue)
LOAD
	ClientAcc,
	[Amount Paid] as [Total Amount Paid]
FROM [your data source];

AmountsDue:
LOAD
	*,
	[Total Available Amount] - [Amount Paid] as [Total Remaining Amount]
	;
LOAD
	*,
	if([Total Available Amount] > [Amount Due], [Amount Due], [Total Available Amount]) as [Amount Paid]
	;
LOAD
	ClientAcc,
	[Due Date],
	[Amount Due],
	[Total Amount Paid],
	if(ClientAcc = peek(ClientAcc, -1), peek([Total Remaining Amount], -1), [Total Amount Paid]) as [Total Available Amount]
RESIDENT tmpAmountsDue
ORDER BY ClientAcc, DueDate ASC;

DROP TABLE tmpAmountsDue;

That will leave you with a few total fields, that you may want to drop.

I've not tried that in Qlik, just typed it straight into the message, so it will no doubt need tweaking, but hopefully you can see the logic.

I'm troubled about the preceding load, and whether that will break the PEEK statement. If it fails let me know how and I will take a look.

Steve

https://www.quickintelligence.co.uk/blog/

[wcilliers]

Hi Steve,

Thank you for your reply. 

I kind of understand the logic, but cannot get it to work. I somehow think the remaining amount needs to be recalculated for every line that gets loaded, but cannot get it to work.

See attached example where I used your logic. It works on the first line. How do I get it to use the remaining amount for the next line instead of total paid again?

Thank you for your help!!

Wynand

[stevedark · Accepted solution]

Hi @wcilliers 

The code you had was slightly different to what I had, in that in the Peek statement to pull forward the remaining you had a calculation. The peek function can only pull forward exactly what is in the field by name. This seems to work:

AmountsDue:
LOAD
*,
[Total Available Amount] - [Amount Paid] as [Total Remaining Amount]
;
LOAD
*,
if([Total Available Amount] > [Amount Due], [Amount Due], [Total Available Amount]) as [Amount Paid]
;
LOAD
ClientAcc,
[Due Date],
[Amount Due],
[Total Amount Paid],
if(ClientAcc = peek(ClientAcc, -1), peek([Total Remaining Amount], -1), [Total Amount Paid]) as [Total Available Amount]
RESIDENT tmpAmountsDue
ORDER BY ClientAcc, [Due Date] ASC;

DROP TABLE tmpAmountsDue;

That requirement to peek a field name rather than a calculation is the reason for the slightly superfluous extra fields. You could get rid of the final preceding load and peek both fields and do the calculation like this:

peek([Total Available Amount], -1) - peek([Amount Paid], -1)

I prefer having the audit trail of the extra field though. The extra fields can be dropped after testing, if you wish.

I've attached a modified version of your app, with an extra client account attached for testing and a table to view the output.

Steve

[wcilliers]

Thanks Steve,

Works like a charm!

Makes sense now. Appreciate the help!

Regards,

Wynand

[wcilliers]

Hi @stevedark ,

Just circling back to the above. The logic you suggested works great if the amount paid is less than the total due amount. But when someone has paid more than what was due, I want to include it in the last 'paid' amount. 

Example:

Lets say this guy paid 8000 then the first two instalments due would be paid up, then the difference will be on the last instalment due . Somehow be able to add the final remaining amount back into the Paid field.

ClientAcc	Due Date	Amount Due	Paid
2089902	30/11/2022	1759.88	1,759.88
2089902	30/12/2022	1759.88	1,759.88
2089902	30/01/2023	1759.88	4,480.24

Would this be possible using the same logic?

Appreciate the help!

[stevedark]

Hi @wcilliers 

Yes, I would have thought so. My gut feel is that it would be a change to this line:

	if([Total Available Amount] > [Amount Due], [Amount Due], [Total Available Amount]) as [Amount Paid]

As well as looking at whether the available amount is larger than the amount due when deciding to put the amount due on the row you would have to also check it against the remaining amount.

Would need time to get my head back to where it was when I wrote the previous code to do that, but I am sure it is possible.

Steve