topic Multiplying Sums in QlikView

Multiplying Sums

Thu, 10 Nov 2011 18:26:41 GMT

We have built a QVDthat provides us the information on what has been ordered on apurchase order, received, vouchered and invoiced. Certain purchase ordershave more than one item on the order. We are trying to developa QlikView that shows the history of that item for all of these stagestogether.

Not all of the orderquantities for each item will arrive at the same time.

PO	Item	Receipt	Voucher	Voucher Qty	Invoice #
35055	A	82506	394731	3600	50004631
35055	A	82506	394770	6323	50004551
35055	A	82506	394797	1200	50004552
35055	A	82536	394797	3600	50004552
35055	A	82776	397759	11400	50005298
35055	A	82776	397761	4800	50005295
35055	A	82776	397763	812	50005296

Receipts

Item	Receipt	Total
A	82506	33369
A	82536	3600
A	82776	51036

When I go to sum the receipts for item A, I am getting an answer that is equal to thesum total times the number of items that were received on that receipt. So in the above examples I have 33,369 instead of 11,123. This has 3items on the receipt. The third line has 51036, but it should be. This occurs for any item that has multiple items on a receipt. Sofor my data on an item, we have vouchered it284,785 of this item, but it is showing me that we havereceived 481,392.

Are we missing something in the design of the QVD or isthere something I need to do instructions regarding the summing that I need todo.

Thank you.

Here is the qvw with the information. PO 35055 has item 78742-20549-C that was receipt 82506. If you look at the Receipt Chart, receipt 82506 has a sum total of 33369 which is three times tht amount that was received on that reciept.

Thanks

Multiplying Sums

Thu, 10 Nov 2011 18:57:55 GMT

When you create the QVD can you do a Load Distinct? Unless you have duplicates in data you will not get this issue.

If you can add couple of expressions as below and share the details?

Count([Voucher Qty] and Count(Distinct [Voucher Qty].

I would be happy to help you if you can share your QVD/QVW.

Regards,

Kiran.

Multiplying Sums

ToniKautto — Thu, 10 Nov 2011 22:07:41 GMT

Please provide a sample QVW to enable evaluation of your data model and expressions. If the data is sensitive, please use the scrambling funciton to make field unreadable.

Settings > Document Properties > Scrambling

Multiplying Sums

Fri, 11 Nov 2011 16:26:47 GMT

I have posted qvw in my ealier posting.

Re: Multiplying Sums

Fri, 11 Nov 2011 16:51:41 GMT

Hi,

There are duplicate records. Quick solution is to use

sum(DISTINCT ReceivedQuantity)

instead of

sum(ReceivedQuantity)

Ideal Solution:

Split Voucher number, Invoice Number and Voucher Quantity into another table with a key on Reciept. Let me know if you need help in doing this.

Regards,

Kiran.