5 Replies Latest reply: Mar 30, 2016 10:50 AM by Sunny Talwar RSS

    Calculate from Upper Row Value

    imren çimen

      I have ıtems and demand.

      I want to find which demands are provided by me. So that ı find total items stock and distribute that by demans and delivery time. Distributing continues untill stock are run out of ıtems.

       

       

      I didn't calculate NewStock and So that PreviousNewStock in Qlik. just find red written row.(first rows of Items).

       

      qwd file and excel are attached.

       

      If(Bstock='BB',Previous(NewStock),Bstock) as BStock. but ı cant do it because of duplicate name Bstock.

       

      How can I Calculate "BStock,Previous(NewStock),NewStock" Rows?

          

      CustomerItemPrevious ItemDATEWEEKBStockPrevious(NewStock)Demandcan be dispatchNew Stock
      BBBzzzzzzzzzzz25.02.20162016-0810228
      BBBxxxxxxxxxxzzzzzzzzzzz25.02.20162016-0820222200
      BBBxxxxxxxxxxxxxxxxxxxx14.03.20162016-11BBBB=previous(newstock)=20022198
      İSTBxxxxxxxxxxxxxxxxxxxx22.03.20162016-12BBBB=previous(newstock)=1985555143
      BBBxxxxxxxxxxxxxxxxxxxx28.03.20162016-13BBBB=previous(newstock)=14344139
      BBBxxxxxxxxxxxxxxxxxxxx28.03.20162016-13BBBB=previous(newstock)=1391010129
      BBBxxxxxxxxxxxxxxxxxxxx28.03.20162016-13BBBB=previous(newstock)=1291616113
      BBBxxxxxxxxxxxxxxxxxxxx28.03.20162016-13BBBB=previous(newstock)=113202093
      BBBxxxxxxxxxxxxxxxxxxxx04.04.20162016-14BBBB=previous(newstock)=932291
      GARxxxxxxxxxxxxxxxxxxxx11.04.20162016-15BBBB=previous(newstock)=911190
      BBBxxxxxxxxxxxxxxxxxxxx11.04.20162016-15BBBB=previous(newstock)=90101080
      BBBxxxxxxxxxxxxxxxxxxxx18.04.20162016-16BBBB=previous(newstock)=802278
      BBBxxxxxxxxxxxxxxxxxxxx18.04.20162016-16BBBB=previous(newstock)=786672
      BBBxxxxxxxxxxxxxxxxxxxx18.04.20162016-16BBBB=previous(newstock)=72101062
      BOYxxxxxxxxxxxxxxxxxxxx18.04.20162016-16BBBB=previous(newstock)=62303032
      BBBxxxxxxxxxxxxxxxxxxxx25.04.20162016-17BBBB=previous(newstock)=322230
      BBBxxxxxxxxxxxxxxxxxxxx25.04.20162016-17BBBB=previous(newstock)=308824
      GÜRxxxxxxxxxxxxxxxxxxxx25.04.20162016-17BBBB=previous(newstock)=2419195
      TELxxxxxxxxxxxxxxxxxxxx25.04.20162016-17BBBB=previous(newstock)=53050
      BOYxxxxxxxxxxxxxxxxxxxx25.04.20162016-17BBBB=previous(newstock)=010000
      BBByyyyyyyyyyxxxxxxxxxx21.03.20162016-1211411444110
      BBByyyyyyyyyyyyyyyyyyyy21.03.20162016-12BB11022108
      DOUyyyyyyyyyyyyyyyyyyyy22.03.20162016-12BB108505058
      BBByyyyyyyyyyyyyyyyyyyy28.03.20162016-13BB582256
      BBByyyyyyyyyyyyyyyyyyyy28.03.20162016-13BB566650
      BBByyyyyyyyyyyyyyyyyyyy28.03.20162016-13BB50141436
      BBByyyyyyyyyyyyyyyyyyyy28.03.20162016-13BB368828
      BBByyyyyyyyyyyyyyyyyyyy28.03.20162016-13BB2830280
      BBByyyyyyyyyyyyyyyyyyyy28.03.20162016-13BB04000
      TELyyyyyyyyyyyyyyyyyyyy28.03.20162016-13BB03400
      GARyyyyyyyyyyyyyyyyyyyy04.04.20162016-14BB0800
        • Re: Calculate from Upper Row Value
          Sunny Talwar

          May be something along these lines

           

          LOAD *,

            If(If(ITEM <> Peek('ITEM'), BStock - DEMAND, Peek('PreviousNewStock') - (DEMAND+Peek('DEMAND'))) < 0, 0,

            If(ITEM <> Peek('ITEM'), BStock - DEMAND, Peek('PreviousNewStock') - (DEMAND+Peek('DEMAND')))) as NewStock,

            If(ITEM <> Peek('ITEM'), BStock, Peek('NewStock')) as PreviousNewStock,

            RowNo();

          LOAD *,

            If(ITEM <> Peek('ITEM'), ApplyMap('app',ITEM,'Not'),'BB') as BStock;

          LOAD * INLINE [

              Customer,ITEM , PreviousItem, DATE, WEEK, DEMAND

              BBB, zzzzzzzzzzz, , 25.02.2016, 2016-08, 2

              BBB, xxxxxxxxxx, zzzzzzzzzzz, 25.02.2016, 2016-08, 2

              BBB, xxxxxxxxxx, xxxxxxxxxx, 14.03.2016, 2016-11, 2

              İSTB, xxxxxxxxxx, xxxxxxxxxx, 22.03.2016, 2016-12, 55

              BBB, xxxxxxxxxx, xxxxxxxxxx, 28.03.2016, 2016-13, 4

              BBB, xxxxxxxxxx, xxxxxxxxxx, 28.03.2016, 2016-13, 10

              BBB, xxxxxxxxxx, xxxxxxxxxx, 28.03.2016, 2016-13, 16

              BBB, xxxxxxxxxx, xxxxxxxxxx, 28.03.2016, 2016-13, 20

              BBB, xxxxxxxxxx, xxxxxxxxxx, 04.04.2016, 2016-14, 2

              GAR, xxxxxxxxxx, xxxxxxxxxx, 11.04.2016, 2016-15, 1

              BBB, xxxxxxxxxx, xxxxxxxxxx, 11.04.2016, 2016-15, 10

              BBB, xxxxxxxxxx, xxxxxxxxxx, 18.04.2016, 2016-16, 2

              BBB, xxxxxxxxxx, xxxxxxxxxx, 18.04.2016, 2016-16, 6

              BBB, xxxxxxxxxx, xxxxxxxxxx, 18.04.2016, 2016-16, 10

              BOY, xxxxxxxxxx, xxxxxxxxxx, 18.04.2016, 2016-16, 30

              BBB, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 2

              BBB, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 8

              GÜR, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 19

              TEL, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 30

              BOY, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 100

              BBB, yyyyyyyyyy, xxxxxxxxxx, 21.03.2016, 2016-12, 4

              BBB, yyyyyyyyyy, yyyyyyyyyy, 21.03.2016, 2016-12, 2

              DOU, yyyyyyyyyy, yyyyyyyyyy, 22.03.2016, 2016-12, 50

              BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 2

              BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 6

              BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 14

              BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 8

              BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 30

              BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 40

              TEL, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 34

              GAR, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 8

              MOB, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 50

              TEL, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 25

              KAR, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 48

              BOY, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 40

              BBB, yyyyyyyyyy, yyyyyyyyyy, 11.04.2016, 2016-15, 2

          ];


          Capture.PNG