6 Replies Latest reply: May 2, 2017 5:42 AM by kakani sai RSS

    Stock analysis

    kakani sai

      Hi Folks ...

      zero days

            I have a doubt about how to show the inventory,

      coming to the point

      i have a ledger table  with me and i want to show Zero inventory days and i'm done with this

      but now i to  want show particular item should not be considered where that item is never sold in that region/ 

      branch due to not available

      lets say for instance item A, item B, item C.

      where A is avail in Region1 and B is available in Region1 & 2,   C is available in only Region3

        • Re: Stock analysis
          Andrey Khoronenko

          Hi,

           

          Can we have a fragment of data and what do you want to see as a result?

           

          Regards,

          Andrey

            • Re: Stock analysis
              kakani sai

              Hi Andrey,

              This is my script for Zerodays

              and now with in this i should consider item which is Zero inventory all the days in Region A but this is avail in another Region B&C

               

              for the item i loaded is 

              'I69195'

              noof days 91

               

              and for particular item it is showing Zero inventory=4 days

              now another couple of items are loaded to check but few among them are zero all 91 days

               

               

               

              stk_temp:

              NoConcatenate

              load brcode,  

                   item_code, 

                   dt,

                    qty

              from [D:\Sai\eco_032000_STOCKLED_2016.qvd]

              (qvd)

              where item_code='I69195'

              ;

               

               

              stk:

              NoConcatenate

              LOAD brcode,  

                   item_code, 

                   dt,

                    qty//,

                   //rangesum(qty,if(item_code=Peek('Item') AND brcode=Peek('brcode'), Peek('Stock'))) AS Stock

                   //if(item_code=Previous(item_code) and brcode=Previous(brcode),peek('cum_qty')+qty,qty) as cum_qty

                  

              Resident stk_temp

              where match(brcode,'002','003') and dt >='01-04-2016' and dt <='30-06-2016'

              //Group by brcode,  

              //     item_code, 

              //     date(ceil(dt))

                   order by brcode,item_code,dt

                  

                   ;

                  

              drop Table stk_temp;

               

               

               

               

              stk_temp:

              NoConcatenate

              load Distinct dt

              from

              [D:\Sai\eco_032000_STOCKLED_2016.qvd]

              (qvd)

              where dt >='01-04-2016' and dt <='30-06-2016'

              ;

               

               

              left join(stk_temp)

              load Distinct brcode

              from [D:\Sai\eco_032000_STOCKLED_2016.qvd]

              (qvd)

              where match(brcode,'002','003') and dt >='01-04-2016' and dt <='30-06-2016'

               

               

              ;

               

               

              left join(stk_temp)

              load Distinct item_code

              from [D:\Sai\eco_032000_STOCKLED_2016.qvd]

              (qvd)

              //where item_code='I69195', and dt >='01-04-2016' and dt <='30-06-2016'

              where match(item_code,'I69195','I65538','I00197','I00303','I00319','I00157','I00158','I00186','I00187') and dt >='01-04-2016' and dt <='30-06-2016'

              ;

               

               

              left join(stk_temp)

              load *

              Resident stk;

               

               

              drop Table stk;

               

               

              stk:

              NoConcatenate

              load brcode,

              item_code,

              dt,

              sum(qty) as qty

              Resident stk_temp

              Group by brcode,

              item_code,

              dt;

               

               

              drop Table stk_temp;

               

               

              stock_temp:

              NoConcatenate

              LOAD brcode,

              item_code,

              dt,

              qty,

              if(item_code=Previous(item_code) and brcode=Previous(brcode),peek('cum_qty')+qty,qty) as cum_qty

              Resident stk

              order by

              brcode,

              item_code,

              dt

              ;

               

               

              drop Table stk;

               

               

              STOCK:

              NoConcatenate

              load

              brcode,

              item_code,

              date(dt) as dt,

              qty,

              cum_qty,

              if(cum_qty=0,1,0) as zero_stock_flag

              Resident stock_temp

              order by

              brcode,

              item_code,

              dt

              ;

               

               

              drop Table stock_temp;

               

               

              store STOCK into 'D:\Sai\Ceo_Dashboard\Ceo_transformed_qvd\STOCK.qvd'

            • Re: Stock analysis
              Devarasu R

              Hi,

              Refer my thread, Thanks

              ABC Analysis in Qlikview

              and also check this,

              Recipe for an ABC Analysis

              ABC Analysis to set and remember classification at runtime

               

              Hope this helps you.

               

              Regards,

              Deva

              • Re: Stock analysis
                kakani sai

                lets say an item is zero for all the days in one region but it is in stock for other regions that item should be considered as item never in stock

                 

                below logic is implemented foy zero inventory days

                 

                stk_temp:

                NoConcatenate

                load brcode,  

                     item_code, 

                     dt,

                      qty

                from [D:\Sai\eco_032000_STOCKLED_2016.qvd]

                (qvd)

                where item_code='I69195'

                ;

                 

                 

                stk:

                NoConcatenate

                LOAD brcode,  

                     item_code, 

                     dt,

                      qty//,

                     //rangesum(qty,if(item_code=Peek('Item') AND brcode=Peek('brcode'), Peek('Stock'))) AS Stock

                     //if(item_code=Previous(item_code) and brcode=Previous(brcode),peek('cum_qty')+qty,qty) as cum_qty

                    

                Resident stk_temp

                where match(brcode,'002','003') and dt >='01-04-2016' and dt <='30-06-2016'

                //Group by brcode,  

                //     item_code, 

                //     date(ceil(dt))

                     order by brcode,item_code,dt

                    

                     ;

                    

                drop Table stk_temp;

                 

                 

                 

                 

                stk_temp:

                NoConcatenate

                load Distinct dt

                from

                [D:\Sai\eco_032000_STOCKLED_2016.qvd]

                (qvd)

                where dt >='01-04-2016' and dt <='30-06-2016'

                ;

                 

                 

                left join(stk_temp)

                load Distinct brcode

                from [D:\Sai\eco_032000_STOCKLED_2016.qvd]

                (qvd)

                where match(brcode,'002','003') and dt >='01-04-2016' and dt <='30-06-2016'

                 

                 

                ;

                 

                 

                left join(stk_temp)

                load Distinct item_code

                from [D:\Sai\eco_032000_STOCKLED_2016.qvd]

                (qvd)

                //where item_code='I69195', and dt >='01-04-2016' and dt <='30-06-2016'

                where match(item_code,'I69195','I65538','I00197','I00303','I00319','I00157','I00158','I00186','I00187') and dt >='01-04-2016' and dt <='30-06-2016'

                ;

                 

                 

                left join(stk_temp)

                load *

                Resident stk;

                 

                 

                drop Table stk;

                 

                 

                stk:

                NoConcatenate

                load brcode,

                item_code,

                dt,

                sum(qty) as qty

                Resident stk_temp

                Group by brcode,

                item_code,

                dt;

                 

                 

                drop Table stk_temp;

                 

                 

                stock_temp:

                NoConcatenate

                LOAD brcode,

                item_code,

                dt,

                qty,

                if(item_code=Previous(item_code) and brcode=Previous(brcode),peek('cum_qty')+qty,qty) as cum_qty

                Resident stk

                order by

                brcode,

                item_code,

                dt

                ;

                 

                 

                drop Table stk;

                 

                 

                STOCK:

                NoConcatenate

                load

                brcode,

                item_code,

                date(dt) as dt,

                qty,

                cum_qty,

                if(cum_qty=0,1,0) as zero_stock_flag

                Resident stock_temp

                order by

                brcode,

                item_code,

                dt

                ;

                 

                 

                drop Table stock_temp;

                  • Re: Stock analysis
                    kakani sai

                    brcode

                    003

                    loaded data for 91 days

                    item_code brcode  zero_days

                    I00197       003       91

                    i00303       003        91

                    i65538       003        91

                    i69195       003        4