Displaying invoice total in pivot table

[#1]

Hello everybody,

I'm new in using QlikView so my question could be banal...

I'm reading from a table with 3 columns: InvoiceID, Discount, InvoiceAmount. The table can have many records for the same InvoiceID because different types of discount can be applied to each Invoice. The InvoiceAmount for th same InvoiceID is always the same. For example:

InvoiceID Discount InvoiceAmount

1 30 200

1 15 200

1 40 200

2 10 500

2 40 500

3 30 300

3 15 300

I've to build in QV a Pivot Table that shows the InvoiceAmount total at the bottom. If I add a simple expression like Sum (InvoiceAmount), I'll receive the sum of the InvoiceAmount column (in the example: 2.200) that is not the real sum of the invoices amount because each amount is replicated. The correct sum should be: 200 + 500 + 300 = 1.000. What is the expression syntax to use for calculate the right sum?

Many many thanks!

Luca

[Anonymous · Accepted solution]

This sort of things usually means that you've joined many-to-one tables, where InvoiceID is in "one" and InvoiceAmount in "many". If this was necessary, you still can get the correct calculation using aggr. Try:
sum(aggr(InvoiceAmount ,InvoiceID))

[#3]

Many Thanks Michael, this solved my problem!

Best Regards,

Luca

[johnw]

Probably better to store these two quantities on separate tables.

Invoice Table:
InvoiceID InvoiceAmount
1 200
2 500
3 300

Discount Table:
InvoiceID Discount
1 30
1 15
1 40
2 10
2 40
3 30
3 15

Any fields associated with the invoice itself would be on the invoice table. Any fields associated with individual discounts would be on the discount table. That will keep summations and the like behaving properly without needing to resort to "strange" expressions.