4 Replies Latest reply: Nov 11, 2011 11:51 AM by Kiran Rokkam RSS

    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

        • Multiplying Sums

          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
            Toni Kautto

            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