Stock Aging - As of Date logic

[#1]

Hi All,

I just stuck in stock aging calculation and i need your view.

I have a data like that.

ID	TYPE	Date	Qty
1	Inventory	01.01.2012	100
1	Inventory	05.02.2012	25
1	Sales	07.02.2012	25
1	Inventory	07.02.2012	20
1	Sales	10.03.2012	25
1	Sales	17.04.2012	25

I try to create a stock aging report for "as of date" logic with FIFO. it should not be latest stock level's aging.

For example; if the business user wants to see the stock aging report for 01.04.2012 it shoud be calculating depending on that date.

In the examples that i find over the community does not care the sales date. it find the latest stock level and redistribute the stock according to that level.

Does anyone has an idea about how to to do that?

thanks.

Omer

[#2]

hi omer,

i dont have the whole picture but im thinking of somthing to be done within the load statment...

try doing an initial load and than use a resident load where you can sort your data by product id and date,

next you will be able to calculate a comulative stock and stock age using the previouse() function.

somthing like:

Stock:

LOAD *,

Date - Previouse(Date) as StockAge;

Resident StockTmp (in this table you initialy loaded your data)

Order by `ID`, `Date`;

Drop Table StockTmp;

this way you can hold a relevant stock level and stock age for each date and that level can be later selected by the end user...

[#3]

Hi,

Thanks for the reply.

Actually, we can not directly look at previous date.. if the previous date is also sales, then you may miss that point.

I seperate positive entries and sales in my script and then redistribute the positive values according to final stock level..

to find remaining stock, i use the below script.

load *, if(%Key_OnHand = peek('%Key_OnHand'),

                    if(peek('Remaining_Stock')>0,peek('Remaining_Stock')-PRIMARY_QUANTITY),OnHandQty-PRIMARY_QUANTITY) as Remaining_Stock

thanks.

Omer

[#4]

i would seperate it to several phases:

1. calculating stock level for stock entry points - for that i wouldnt have exclude the sales but multiplie them by (-1) to get the updated stock.

2. after we have the updated stock for all dates, in the resident query i would insert a 'where' in the resident to exclude the sales, only after we calculated the relevant stock level.

3. i would also use qualify to make sure this data doesnt mix with other data because its a summerized data.

this should be very simple.....

[#5]

yes, phases that you are talking are very simple and straigth forward...

but in this case, you miss one point; when you create a summary view according to + and -

to find the final stock level, you could not say this 100 stock comes from which stock entry.

to do that you should create a loop for each item for each day.. since the data volume that i am dealing is so huge, i need to find some other way to handle this case.

i attach a simple QV document for your reference to make it clear for you.

In my sample data set, first 2 entries are + and they are 100, 25. after those entries there is a sales which is 25 and stock level drops to 100..

if i do your suggestion; my final stock level will seen as 100 and my first stock level will seen 100 and it says, it is related with that.. but according to FIFO (First In First Out), my 25 sales should be substract from my first inventory entry which 100..

I hope it will be much more clear for you now...

[nilesh_gangurde]

Hi omerbsgme,

You got the way out for this. ??

please share.

-Nilesh