3 Replies Latest reply on Dec 7, 2017 9:02 AM by Shinichiro Murakami

    Help me please! Summing Qty by Order instead of by Line

    Thomas Fenwick

      Hello all!  I have been looking around the forums but I can't find anything that is helping me directly.

       

      I have attached my data set, and you'll find 3 orders with various numbers of lines on each order.  I am trying to filter these orders into three types, ones that have completed shipping all products, ones that haven't started shipping, and ones that are partially shipped.  What I need to do is sum the total [Quantity Ordered] on an order and compare it to the [Quantity Invoiced] on that order.  If they are equal, the order is complete.  If the sum of Quantity Invoiced = 0 then the order hasn't shipped anything yet.  If the Quantity Invoiced is >0 but <Quantity Ordered then it's partially shipped.

       

      My problem is that I can create a calculated field using IF, ELSEIF statements, but it's only summing the quantities of the LINES, not the whole ORDERS.  I feel like I need to group by OrderCode and do the IF statement so that it sums all lines but I can't figure how to do that.

       

      In my example data, OrderCode 1BSGQWH should be flagged as completed, OrderCode 1BSJHP8 should be flagged as Not Started and finally OrderCode 1BSKVN3 should be a Partial.

       

      Thoughts?

       

      Message was edited by: Thomas Fenwick to replace the server connected worksheet with a screen shot