3 Replies Latest reply: Jul 4, 2013 7:48 AM by Janne Vauhkonen RSS

    Monthly Inventory report based on SAP data

      Hi

       

      I am trying to build monthly inventory report from the SAP. There is "current stock" in SAP and all inventory movements from history. I would need to build a logic in expression (in expression I think is easiest?) that would calculate monthly stock levels based on these inputs. Any ideas how to write that?

       

      I have attached simple example of situation. I have there column "Stock" in where I would like to see Month end inventories. In this example I have values 5500, 9000, 7000, 8000 which I would like to see under month 5 because it is month end inventory of that month. In a same way I want to see figures 4000, 7000, 5000 & 8000 as month end inventoy (in column stock) in period 4.

       

      I hope I was clear with my problem.

       

      Thank you very much already beforehand!

       

      Regards

      Janne

        • Re: Monthly Inventory report based on SAP data
          Jonathan Brough

          You can:

          1. Set up a date island that contains combinations of all the dates and relevant dimensions (e.g. ProductID)

          2. Set up an AccumulationKey alongside you Movements data, which you can test for to see if the month or date has changed the data

          3. Load the Movement data into the date island, in reverse order, testing for whether the Accumulation key has changed. If it has (and a new month or date is being loaded) then you can load in the previous balance minus the sum of the movements

           

          See this excellent example: http://community.qlik.com/message/100042#100042

          If does the accumulation going forwards in time, but can be changed to go backwards in time.

          Jonathan

            • Re: Monthly Inventory report based on SAP data

              Thanks!

               

              I think this might be excatly what I was looking

               

              Regards

              Janne

              • Re: Monthly Inventory report based on SAP data

                Hi,

                 

                I have a problem with this logic. Obviously I am doing something wrong, but I can't see where.

                 

                I adapted your logic, but for some reason I can't get anything into monthly opening balance.

                 

                Below is the script I am using in inventory accumulation calculation. I have also attached a picture from table view, in where you may see my problem

                 

                invproblem.JPG

                 

                The script:

                 

                InventoryAccumulations:

                 

                 

                LOAD distinct

                AccumulationKey,

                MonthlyMovementKG,

                if(rowno() = 1 OR AccumulationKey <> peek(AccumulationKey), 0, peek(ClosingAmount)) AS OpeningAmount,     

                if(rowno() = 1 OR AccumulationKey <> peek(AccumulationKey), MonthlyMovementKG, peek(ClosingAmount) + MonthlyMovementKG) AS ClosingAmount,

                 

                Plant,

                Material_MATNR,

                MoveYear,

                MoveMonth,



                date(makedate(MoveYear, MoveMonth), 'MM-YY') AS InvPeriod

                RESIDENT

                InventoryAccumulationsTemp;

                 

                 

                 

                Many thanks already beforehand!

                 

                Regards

                Janne