I'm looking for a simple way to calculate interests on overdue receivables that will cover several interests rates in apropriate periods e.g. invoice raised on 26 May 2006 for 100USD would be calculate as following
Are you sure you want simple interest rather than compounded interest? That seems very unusual to me. But if it's really what you want, I'll get you started:
"Invoice Amount" * ( 1 + if(len("Overdue On") < 1 or "Overdue On" > now(0), 0 ,sum(aggr( "Interest Rate" * // calculate the number of days overdue in the period here / ("Period End" - "Period Start"), Period)))
The trick here, if you can call it one, is the sum(aggr(...,Period)), which essentially loops through all of the periods and sums up the calculated amounts of interest for each.
I meant coumpound interests in a simple way 🙂 How should I put the code to use the below table to calculate invoice of 100 due on 2004-01-02. I didn't quite get what you wrote in the last post?
I'm going to have to get back to you on this as I'm particularly busy right now. I'm guessing that your interest compounds daily, then? If so, I'd probably use the current interest rate table to build a daily interest rate table. Then rather than sum the interest across the aggr(), you'd need to multiply it. I don't see a product(aggr(...,"Date")) function to do that, so I'm not sure how I'd write it. You'd also want to look closely at the available financial functions, as perhaps this is already built into QlikView. I might be able to look at this in more detail some time next week, and try to come up with an example. Or perhaps someone else can come up with a good solution before then.
And just to confirm, here are several approaches for calculating the final amount due on $1000 borrowed at 10% interest for a year with no payments:
I don't yet have an expression for this, but I've been thinking further on it, and I think we may be off track from an overall system standpoint.
If you're actually sending out invoices and collecting payments, you should have an accounts receivable system. The accounts receivable system should be calculating the amount due, not QlikView. QlikView should merely be reporting the amount due given to it by the accounts receivable system.
If you don't have an accounts receivable system, get one. I highly recommend against using QlikView as an accounts receivable system. The reason has to do with QlikView's internal numeric format, which makes it a poor choice when accounting rigor is required. I would NEVER use QlikView to actually manage my books - general ledger, accounts payable, accounts receivable and so on. See my Wiki entry regarding mathematical errors in QlikView for further information:
If this is just a school project, or something you were curious about, or some summary report where it is OK to be off by a few pennies here and there, OK. But if this is truly how you want to track your receivables, I think you may want to rethink that.
