Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

SYSTEM MAINTENANCE: ** Thurs., Sept. 19, 1 AM ET,** Platform will be unavailable for approx. 60 minutes.

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Analytics
- :
- Re: Calculating Balance Due

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Showing results for

wcilliers

Partner Ambassador

2023-04-13
09:08 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,032 Views

1 Solution

Accepted Solutions

stevedark

Partner Ambassador/MVP

2023-04-14
03:20 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

6 Replies

stevedark

Partner Ambassador/MVP

2023-04-13
01:11 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Partner Ambassador

2023-04-13
08:34 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Partner Ambassador/MVP

2023-04-14
03:20 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Partner Ambassador

2023-04-17
03:40 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks Steve,

Works like a charm!

Makes sense now. Appreciate the help!

Regards,

Wynand

wcilliers

Partner Ambassador

2023-05-18
07:58 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!

890 Views

stevedark

Partner Ambassador/MVP

2023-05-19
06:51 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

884 Views

Community Browser