Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Multiplying Sums

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

4 Replies
Not applicable
Author

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.

ToniKautto
Employee
Employee

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

Not applicable
Author

I have posted qvw in my ealier posting.

Not applicable
Author

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.