Amount Due - Paid amount?? Please help me out

[#1]

Hi,

I hope somebody can help me out. I have the following straight tabel in Qlikview:

Company	Invoicenumber	Description	Date	Exp. Date	Invoice amount	Amount Due
421 - Dell	11111	Part of payment		1-2-2010		-1.325,89
421 - Dell	11111	Rent  october 2014	1-2-2010	1-3-2010	3.957,35
				Total Debtors	2.631,46

What I want is the following:

Amount due = Amount Due - Invoice amount from the first example

Company	Invoicenumber	Description	Date	Exp. Date	Invoice amount	Amount Due
421 - Dell	11111	Rent  october 2014	1-2-2010	1-3-2010	3.957,35	2.631,46
				Total Debtors	2.631,46

thank you in advance

[chrismarlow]

Not sure you are going to get exactly the format you are after, but this may be a start. I think in your straight table you need all of the fields except Company and Invoice Number to be expressions.

The dates would be Max(the relevant field).

For Description use FirstSortedValue(Description, -[Exp. Date]).

For the Amount Due add the original Amount Due and Invoice Amount together inside the Sum (might have issues with nulls).

[sunny_talwar · Accepted solution]

Is this what you want? PFA

Best,

S

[#4]

Almost, I only want the last line in your example:

421 - Dell

11111

Rent October 2014

01/02/2010

01/03/2010

3957.35

2631.46

To be more clear I don't want to see part of the payment anymore.

[sunny_talwar]

I am guessing you are going to use some kind of criteria to decide which line to keep and which one to remove. Is it always the most recent date that will be kept and rest of the line items will be removed?

Best,

S