2 Replies Latest reply: May 30, 2018 7:14 PM by David Forest RSS

    Stock count

    Raman Mastana

      Hi,

       

      I require some assistance on the best way to get a total stock count by month-end for each month.

       

      Below are two tables I have which show stock in and stock out. I am unsure as to how the SQL should be written in Qlik Sense data load editor to accurately display stock count for each month end.


      Calculation of the stock at end of the month: =Previous month stock - stockout + stockin

       

      Stock

      PartIDSerialNoTotalQtyCreatedDate
      1A1234512017-12-01
      2B1234512018-01-01
      3C1234512018-02-05
      4D1234512018-02-20
      5E1234512018-04-02

       

      StockOut

      PartIDQtyOutDate
      112018-02-02
      212018-03-02
      312018-05-01
      412018-05-25
      51NULL

       

      StockIn

      PartIDSerialNoTotalQtyReceivedDate
      6G1234512018-02-01
      7H1234512018-02-20
      8I123451

      2018-03-03

      9J1234512018-04-01

       

      The output I would like:

       

      YearMonthTotalStock
      2017Nov?
      2017Dec?
      2018Jan?
      2018Feb?
      2018Mar?
      2018Apr?
      2018May?

       

      Thank you in advance for any responses.