Stock Ageing Analysis

[#1]

Dear All,

I have a typical requirement that my user want to analyze the pending stock in the inventory with the help of aging. This is FIFO method.

Take for example

The 15 nos. quantity has been received into the stock from manufacturing company on 12/08/2009.

(-3) Qty has been transfer out from the stock on 20/8/2009

(-10) Qty has been transferred out from the stock on 10/09/2009

7 Qty has been received into stock on 25/09/2008 and received 2 more bunch of qty into stock on 29/09/2009

If I run my report on 30/09/2009 I should get on 0-30 slabs 9, 31-60 slabs 2 and 61-90 slabs 0.

Calculation would be as below:

1^st received qty 15 has to be minus with stock transferred qty until unless the stock in hand become zero

.i.e. 15 -3 -10 = 2 (Date for this is 12/08/2009)

When we run a report on 30/09/2009 above 2 has to be in 31-60 slab and other positive value should be in 31-60 value.

Please find the excel sheet with this mail and the required o/p and qlikview o/p which i`m getting now

Thanks in advance.

Regards

Sridhar E

[#2]

I am also interrested to know how to deal with stock Ageing

[#3]

hi

please find attached file, where i have tried to solve your problem. As of now data is coming as per your requirement but think you have to fine tune it accordingly

Regards

Peter

[#4]

hi

please find attached xls file which is uploaded

go to table files in the script and browse for this file, then clik next and enable transformation step, here add a new column (RecDate) which fills date where qty is positive and leaves the -ve qty as blank, then it fills these blank cells with previous value which will be the last date.Hence we wiil get the rec date against each record, now create slabs as per your requirement

if not clear, just save the attached file on the path and reload the model

Regards

Peter

[#5]

hi csavgssc,

Your solution is not an answer. Switch 7 with -10 in the source data and you'll see why.

[#6]

Doing in script is pretty easy, but it's not flexible (what if I want to see the status at a earlier date?):

1. Calculate final stock

2. Separate positive transactions

3. Distribute final stock among last positive transactions.

See attached file as example.

I want to do it in interface, just using formulas. I've reached a point but it works only with one product. I'll keep working.

[#7]

Hi Lucian,

I have checked the value for 2 products, its working fine but only issue is that its restricting transactional data according to the user selection date.

I would request you to help me out with this issue and i`m ataching the file which i tested the same scenario.

Thanks in advance.

Regards

Sridhar

[#8]

Please explain in detail what the problem is. I don't understand what "restricting transactional data according to the user selection date" means.

[#9]

Its better to show two graphs or analysis reports, from the same selection criteria, one graph or report should display information for what is selected, while the other shows that was not selected, this can be done through select excluded values. and its simple.