Announcements
cancel
Showing results for
Did you mean:

## Calculating Balance Due

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

Labels (4)

• ### Set Analysis

1 Solution

Accepted Solutions

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:
*,
[Total Available Amount] - [Amount Paid] as [Total Remaining Amount]
;
*,
if([Total Available Amount] > [Amount Due], [Amount Due], [Total Available Amount]) as [Amount Paid]
;
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

6 Replies

This doesn't sound that unique!

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

``````tmpAmountsDue:
ClientAcc,
[Due Date],
[Amount Due]

LEFT JOIN (tmpAmountsDue)
ClientAcc,
[Amount Paid] as [Total Amount Paid]

AmountsDue:
*,
[Total Available Amount] - [Amount Paid] as [Total Remaining Amount]
;
*,
if([Total Available Amount] > [Amount Due], [Amount Due], [Total Available Amount]) as [Amount Paid]
;
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/

Author

Hi Steve,

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?

Wynand

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:
*,
[Total Available Amount] - [Amount Paid] as [Total Remaining Amount]
;
*,
if([Total Available Amount] > [Amount Due], [Amount Due], [Total Available Amount]) as [Amount Paid]
;
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

Author

Thanks Steve,

Works like a charm!

Makes sense now. Appreciate the help!

Regards,

Wynand

Author

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!

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