2 Replies Latest reply: May 15, 2014 10:14 AM by lcwalker _ RSS

    Summary Help: Sum quantities

    lcwalker _

      Good afternoon.  I have table data in the following format

       

      Location     Item    OnHand

      Station1     123           5

      Station2     123           2

      Station1      ABC          1

      Station2      ABC          2

      /CODE

       

      What I want to do is create a summary that shows

      Location     Item    OnHand  TotalOnHand

      Station1     123           5           7

      Station2     123           2           7

      Station1      ABC          1           3

      Station2      ABC          2           3

      /CODE

       

      I want to have a column that shows the total on hand for each item in all locations, is this possible?  I've tried cumulative sum, but it doesnt isolate it by the item number, it sums every row.

        • Summary Help: Sum quantities
          Eamon Donoghue

          Hi lcwalker,

          I believe the best approach to your problem is break it down into two stages. 

           

          Stage 1:  Read the report and create a Summary which contains totals for each product.  Create an export (e.g. an Excel file) of this Summary - say ItemTotals.xls.  This export will have two columns: Item and TotalOnHand and based on your example above would look like the following:

           

          Item               TotalOnHand

          123                      7

          ABC                     3

           

          Stage 2:  Open the report as normal and use an External Lookup to import the Excel file created at Stage 1 (ItemTotals.xls), giving you a TotalQOH field containing the additional data you require.

           

          If you haven't used External Lookups before, they're pretty straightforward - you simply need to link the two sets of data using a Key ( in this case Item) and you can specify which fields you need to import - (here it will be TotalOnHand).

           

          If all of this works as required, it is quite simple to automate the two sub-processes using a batch file or script.

           

          If you need any further help, please feel free to get back to the forum or PM me.

           

          hth.

           

          Best regards,

          Eamon.