topic Re: Sum issues with particular data in QlikView

Sum issues with particular data

Tue, 25 Sep 2012 10:05:43 GMT

Hey everyone,

im new to qlikview and i have some problems figuring out of to manage some exceptions i have found in some reports i need to work on.

Info:

Purchuase NO is the number of the purchuase order

Requisition NO is the number of the requisition

Invoice NO is the number of the invoice

PO_Line is the is the line of the purchuase order

Payment_amount is the amount PAID for the invoice

Amount is how much has been paid for every PO_LINE

Amount_paid is the total amount PAID for the invoice

Situation:

My firm uses a ERP Oracle and uses QlikView

. Now my problem is when the report sum(invoice_amount). The problem is caused by some invoices that including more than one PO_LINE_ID that multiply the total for the numbers of PO_LINE_ID.

I cant really figure out how to solve it. If someone can help me will be very appreciated.

See the sample i created to see the situation.

Thank Everyone in advance.

Juan Pedro

Re: Sum issues with particular data

Tue, 25 Sep 2012 10:18:37 GMT

Take out the PO_LINE_ID from the report

Or use the DISTINCT function

Re: Sum issues with particular data

Tue, 25 Sep 2012 10:25:02 GMT

Hey thank you for your anserw

Distinct is not an option the database contains multiple invoices for the same value and loosing them will false the analisys completely.

For the PO_LINE_ID option already tried that but dosent change much. This is only a part of the report and the lines are needed for another part of the analisys. I need to keep the fields.

Waiting for more options

Cheers,

Juan Pedro

Re: Sum issues with particular data

Tue, 25 Sep 2012 10:27:54 GMT

Try taking away the aggregate function "sum" and just use "invoice_amount"

Re: Sum issues with particular data

Tue, 25 Sep 2012 11:54:37 GMT

This "partialy" fixes the problem since i can actualy see only the invoices

PROJECT	INVOICE_NUM	INVOICE_AMOUNT
1498	1	6329
1498	2	7911.13
1498	3	1582.14

Thing is i need to sume the invoices so marketing people how much is the total of the invoices and thats what's giving problems at the moment.

Thank you for your time and help if anyone else has an idea on how to solve this problem im all hears

Juan Pedro