topic Calculate Invoice Amount in Invoice Header in QlikView

Calculate Invoice Amount in Invoice Header

salto — Tue, 24 May 2011 09:38:00 GMT

Hello,

In the data source, there is no Amount field in the Invoice Header, it is calculated by making a sum of the Sales Lines table "Amount" field.

But I would like to get that Invoice Total in the initial script (I can do it in the charts, but I'd like to have it in the script).

Is there any aggregation function I could use for this purpose? Many thanks in advance.

Calculate Invoice Amount in Invoice Header

kaushiknsolanki — Tue, 24 May 2011 09:52:51 GMT

Hi,

Can you please provide the data file.

Regards,

Kaushik Solanki

Calculate Invoice Amount in Invoice Header

RicardoRamos — Tue, 24 May 2011 09:56:51 GMT

Hi,

you can load the invoice detail, after that create a temporary table with InvoiceID and Sum(amount) and use that table to do a left join with the header table.

Regards,

Calculate Invoice Amount in Invoice Header

salto — Tue, 24 May 2011 14:44:28 GMT

Hello Ricardo,

Thank you. Which function should I use to Sum the total for every InvoiceID? This seems no to be correct:

AuxTotals:
load
InvoiceID,
Sum(total InvoiceID Amount) as InvoiceTotal
resident InvoiceLines;

Regards,

Calculate Invoice Amount in Invoice Header

Tue, 24 May 2011 14:46:36 GMT

AuxTotals:
load
InvoiceID,
Sum(total InvoiceID Amount) as InvoiceTotal
resident InvoiceLines

group by InvoiceID ;

That should solve your problem..use group by

Regards

sravan

Calculate Invoice Amount in Invoice Header

rwunderlich — Tue, 24 May 2011 16:42:49 GMT

You can use an anonymous table in the join and skip creating and dropping the extra table. For example:

LEFT JOIN (InvoiceHeader)

load
InvoiceID,
Sum(total InvoiceID Amount) as InvoiceTotal
resident InvoiceLines

group by InvoiceID ;

-Rob

Calculate Invoice Amount in Invoice Header

salto — Wed, 25 May 2011 06:25:34 GMT

Many thanks to all of you, it worked!