topic Simple Aggregation question in QlikView

Simple Aggregation question

m4u — Mon, 18 Jan 2010 18:37:40 GMT

Hello

I have two tables - Invoices and Receipts. The Receips table has an InvoiceID field linking it to the invoice.

In the receipts table I have two fields - Applied to Invoice amount, and Total Receipt amount. For a single receipt, Total Receipt Amount=SUM(Applied to invoice) amount

There is a many to many relation here - meaning a single invoice can be related to multiple receipts and a single receipt may be related to multiple invoices.

I want to show a list of receipts in a datatable - receipt number and Total Receipt amount

If I were to do it in SQL, I would do SUM(TotalReceiptAmount) .... GROUP BY ReceiptID

In QV, If I do SUM(TotalReceiptAmount) I get the wrong figure since there are several lines for each receipt in my logical table. If I do SUM (DISTINCT TotalReceiptAmount) its better, but still not correct since if I have two receipts with same TotalReceiptAmount (for instance, 100), it will sum them as 100 instead of 100.

How can I specify QV to consider a single record from each ReceiptID?

Thanks

Simple Aggregation question

Mon, 18 Jan 2010 20:37:51 GMT

Try a


Sum(TOTAL <ReceiptID> TotalReceiptAmount)

Sum total of TotalReceiptAmount per ReiceiptID (group by)

Simple Aggregation question

m4u — Mon, 18 Jan 2010 20:46:40 GMT

Doesnt work.. still showing duplicated totals

Simple Aggregation question

Mon, 18 Jan 2010 21:06:19 GMT

You probably need to JOIN these tables first.

Simple Aggregation question

m4u — Mon, 18 Jan 2010 21:16:41 GMT

They are attached

I am attaching the reduced file - please take a look if you can. The drill down tab, the receipts table has only 1 receipt (as it should) but the total is wrong. The receipts table contains multiple records for the same receipt id.

Simple Aggregation question

Mon, 18 Jan 2010 22:10:19 GMT

Hi,

Why do sum in that case. Just have the expression as

ReceiptTotal

Simple Aggregation question

m4u — Tue, 19 Jan 2010 08:21:38 GMT

Great, it worked!