Stock analysis

[#1]

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

[ahaahaaha]

Hi,

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

Regards,

Andrey

[devarasu07]

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

[#4]

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

(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

(qvd)

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

;

left join(stk_temp)

load Distinct brcode

from

(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

(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;

[#5]

brcode

003

loaded data for 91 days

item_code brcode  zero_days

I00197       003       91

i00303       003        91

i65538       003        91

i69195       003        4

[#6]

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

(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

(qvd)

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

;

left join(stk_temp)

load Distinct brcode

from

(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

(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'