1 Reply Latest reply: Sep 22, 2017 5:27 AM by Shraddha Gajare RSS

    Calculate Opening Stock

    manisha Patidar

      Hi All,


      Can you please suggest on below requirement!!!

      I have a item ledger entry table containing  [posting date](Date field),  [Quantity field]

      Need to build a report  which displays data month wise for last 1 year from date selected by user.


      Example if user selects date as 15-06-2017 then data should be displayed from 15-06-2016 to 15-06-2017 in below format.

      june2016 , july 20166..........................june 2017

      Logic to  calculate opening stock

      Opening stock for current month is summation of Quantity up till previous month.

      Example - For  june 2016 opening stock will be summation of quantity for all months and years which is present in database less than  equal to 31 may 2016.

      i.e All dates less than equal to 31may 2016. and similar approach will be followed for all months

      Kindly suggest on the same urgently.

      Thanks in advance!!!...:)


        • Re: Calculate Opening Stock
          Shraddha Gajare

          For Monthly Stock you can do this in Script..


          Let vMin = num(MakeDate(2008,03,30));     // Use minimum date of Database

          let vMax = num(MonthStart(today()));

          Load date($(vMin) + RowNo() -1) as Date,
               monthstart( Num(MonthStart(date($(vMin) + RowNo() -1)))) as MonthStart
          AutoGenerate 1
          While date($(vMin) + RowNo() -1) < date($(vMax));


          Load Distinct
          MonthStart as Date
          Resident Temp;


          Drop table Temp;


          Let vCount = NoOfRows('TEMP');

          For i=0 to $(vCount) - 1


          LET vDATE = NUM(PEEK('Date',$(i),'TEMP'));




                 /* Here you can add other fields at which level you want to calculate Opening Stock. For Ex. Material, Location, Item Etc. If you include any field here also include it in Group By clause*/


                    Date($(vDATE)-1) as DATE   ,  
                   SUM(Quantity) as QUANTITY
          FROM [lib://Qvds/InventoryRawData.qvd]

          where Date("Posting Date") < date($(vDATE))
          Group by
                   "Posting Date";