1 Reply Latest reply: Mar 10, 2013 4:30 AM by Diamantis Archontoglou RSS

    How to get beginning and ending inventory using Set Analysis?

      Say that I have these tables:

       

      SELLING CALENDAR: (Fiscal Calendar)

       

      MONTH YEARSTART DATEEND DATE
      NOV-201110/26/201111/25/2011
      DEC-201111/26/201112/25/2011
      JAN-201212/26/20111/25/2012
      FEB-20121/26/20122/25/2012
      MAR-20122/26/20123/25/2012

       

       

      ITEM INQUIRY: (This is the daily inquiry of available Quantity)

       

      DATE INQUIRYITEMAVAILABLE
      12/25/2011ITEM A55
      1/25/2012ITEM A90
      2/25/2012ITEM A107
      3/25/2012ITEM A141

       

       

      PURCHASE ORDER PLAN: (This is only plan quantity)

       

      PLAN DATEITEMORDER PLAN
      1/20/2012ITEM A50
      2/15/2012ITEM A40
      3/10/2012ITEM A45

       

       

      RECEIVED ORDER: (This will be based on order plan (sometimes delivered are less, sometimes more than the ordered - but this is ok, dont mind the QTY)

       

      RECEIVED DATEITEMRECEIVED QTY
      11/25/2011ITEM A10
      12/24/2011ITEM A30
      1/20/2012ITEM A45
      2/16/2012ITEM A40
      3/11/2012ITEM A50

       

       

      ACTUAL SALES: (This is actual sales)

       

      INVOICE DATEITEMINVOICE QTY
      12/23/2011ITEM A5
      1/25/2012ITEM A10
      1/30/2012ITEM A23
      3/20/2012ITEM A16

       

       

      BELOW IS WHAT I WANTED TO HAVE:

       

      Beginning Inventory: FORMULA: Last Month Ending Inventory or Last Month Selling Date in Item Inquiry

                                    -but what I want here is that, as much as possible there's no use of above() functions, I want to put exactly what's last month ending                                inventory formula, so that when I select only one month, I still get the actual beginning inventory. I don't want the BEGINNING                               INVENTORY turns 0 when only one month is selected.

       

                                    Say for example: when month is January, the result should get

                                    DECEMBER BEGINNING INVENTORY - DECEMBER ACTUAL SALES + NOVEMBER RECEIVED ORDER

                                    - so that the result will be fix and not change to ZERO when only one month is selected, but if there's no way to get that, then let's straight                               forward, BEGINNING INVENTORY will be LAST MONTH ENDING INVENTORY

       

                                    Beginning Inventory is the available balance (see item inquiry)  for last ending date of sales (see selling calendar)

                                   

                                    Is it applicable here to apply set analysis?

       

      Ending Inventory: FORMULA: This Month Beginning Inventory - Actual Sales + This Month Received Order

       

      Actual Sales: FORMULA: This Month Actual Sales

       

      Purchase Order Plan: FORMULA: This Month Order Plan

       

      Received Order: FORMULA: This Monthh Received Order

       


      JAN-2012FEB-2012MAR-2012
      Beginning Inventory5590107
      Actual Sales102316
      Purchase Order Plan504045
      Received Order454050
      Ending Inventory90107141

       

      January Beginning is: December ending or Last selling date of December

                                      55

       

      January Ending is:     Jan Beginning - Jan Actual Sales + Jan Received Order

                                      55 - 10 + 45 = 90

       

      Februay Beginning is: Jan Ending Beginning or should it be possible to apply the full formula of getting JANUARY ending?

       

      So the question is: how can I get Feb as 90 without using above() function? is it possible? Can I able to comeup this? Or please help, either way, as long as this can be resolve.

       

      Thanks,

      Bill

        • Re: How to get beginning and ending inventory using Set Analysis?
          Diamantis Archontoglou

          Hi

           

          My point of view is that is not an issue to be solved using set analysis. I would try to solve it in the script and save the results directly to a qvd file. For example:

           

          Lets say that you need to build the rolling qty per product while you read the initial table you should build

          an accumulation key like the following one in order to build break levels:

           

           

          Load company & '|' & product & '|' & Year AS AccumulationKey, PeriodQty as Qty_trans...

           

           

          Then you sould reference to that resident table and build opening and closing figures with the following technique:

                     

           

          FinalStock:

          LOAD

          product,

               Year,   Month,  AccumulationKey, Qty_trans,

          if(rowno() = 1 OR AccumulationKey <> peek(AccumulationKey), 0, peek(ClosingQty)) AS OpeningQty,

          if(rowno() = 1 OR AccumulationKey <> peek(AccumulationKey), Qty_trans, peek(ClosingQty) + Qty_trans) AS ClosingQty,

                  if(rowno() = 1 OR AccumulationKey <> peek(AccumulationKey), Qty_Weight, peek(ClosingWeight) + Qty_Weight) AS ClosingWeight,

           

          RESIDENT

          TempSum

          ;

           

          On the above code for the 1st row the opening amount is 0 and whenever there is a break level

          on accoumulation key the opening amount is set to 0 again. Into the same level period qty is accumulates to closing quantity which becomes the opening qty of the next month.

           

          I hope it helps...