Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Stock analysis

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

5 Replies
ahaahaaha
Honored Contributor

Re: Stock analysis

Hi,

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

Regards,

Andrey

devarasu07
Honored Contributor II

Re: Stock analysis

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

Not applicable

Re: Stock analysis

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;

Not applicable

Re: Stock analysis

brcode

003

loaded data for 91 days

item_code brcode  zero_days

I00197       003       91

i00303       003        91

i65538       003        91

i69195       003        4

Not applicable

Re: Stock analysis

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'

Community Browser