We would like to find out the ageing of invoices - customer wise. A Customer can have multiple invoices at different dates and there could be many payments (which may not be directly related to the invoices).
For example consider the following:
Customer Name: ABC CORP
Invoice No. Invoice Date: Credit Days Due Date Amount
INV001 01/01/2011 30 Days 01/31/2011 USD 1000.00
INV002 02/01/2011 30 Days 03/04/2011 USD 1200.00
INV003 04/01/2011 30 Days 05/01/2011 USD 900.00
Now there is a payment made which is not specific to any invoice. The payment date here is not relevant. Paid amount is say USD 1200.00.
My condition is, first the payment should knock off the Amount of Invoices on FIFO basis. i.e., in this case, the payment will knock of the first invoice and it should not be taken into account at all. After knocking off the first invoice, there will be an amount of USD 200.00 still remaining. This should be taken into consideration for the next invoice. So, in this case, now the outstanding amount on the INV002 will be only USD 1000.00 after knocking off USD 200.00.
Now…the ageing should display USD 1000.00 under 60-90 days bucket and the amount related to INV003 should get displayed under 0-30 days bucket.
Can you please help us in this situation on how this can be done in qlikview.
Thanks
Napolean
