Stock ageing

[anil2185]

Hi Folks,

I am trying to make stock ageing in qlikview. Please help me out.

I have a scenario like this:-

Item is xyz.

Purchase Date          QtyIn         Sale Date       Qty out    

01.01.2014               10              01.02.2014          1

                                                 05.03.2014          2

01.04.2015                 5              10.04.2015          5         

Output Required:-

Item          <30 Days               30-60 days          60-90 days          >90 days

xyz               5                                                                                2

Thanks in advance.

Anil

[ankitaag]

Hi Anil,

With the help of Class function you can create these aging buckets easily.

Thanks and Regards,

Ankita

[anil2185]

Hi Ankita,

Thanks for reply, but buckets are not the issue. Main thing is that the quantity under buckets should come on FIFO basis. Please see the output carefully.

Regards,

Anil

[jagan]

Hi,

Try like this

LOAD

*,

If(Days < 30, '<30 Days',

     If(Days <= 60, '30-60 days',

     If(Days <= 90, '60-90 days',  '>90 days'))) AS Bucket;

LOAD

*,

Num(SaleDate - PurchaseDate) AS Days

FROM DataSource;

Now in chart you can use

Dimension: Bucket

Expression: Sum(QtyOut)

Hope this helps you.

Regards,

Jagan.

[anil2185]

Hi Jagan,

Thanks for reply. I need to do ageing for balance stock only.

Regards,

Anil

[jonathandienst]

Hi

Something like this (just replace the Stock table with your data):

// Aging buckets

Intervals:

LOAD * Inline

[

  From, To, Age

  0, 29, <30 days

  30, 59, 30-60 days

  60, 89, 60-90 days

  90, 1000, >90 days

];

// Source data (replace with actual data load)

Stock:

LOAD *,

  RowNo() As RowID

Inline

[

  Date, Item, QtyIn, QtyOut

  2015/01/10, A, 40, 0

  2015/02/12, A, 20, 0

  2015/03/12, A, 20, 0

  2015/04/06, A, 25, 0

  2015/04/10, A, 5,  0

  2015/01/30, A,  0, 30

  2015/02/10, A,  0, 5

  2015/03/05, A,  0, 10

  2015/04/05, A,  0, 10

  2015/04/20, A,  0, 20

];

// FIFO, so just get all outs

T_Out:

LOAD Sum(QtyOut) As TotalOut

Resident Stock;

Let zOut = Peek('TotalOut');

DROP Table T_Out;

// Aging calculation

Join(Stock)

LOAD

  RowID,

  Date,

  If(QtyIn > 0, Today() - Date) As DateAge,

  QtyIn + Alt(Peek(CumulativeIn), 0) As CumulativeIn,

  RangeMax(0, QtyIn + Alt(Peek(CumulativeIn)) - $(zOut)) As NetPosition

Resident Stock

Order By Date;

// Create Age Buckets (and clean up syn key)

Left Join(Stock)

IntervalMatch(DateAge)

LOAD From, To Resident Intervals;

Left Join(Stock)

LOAD * Resident Intervals;

DROP Fields From, To FROM Stock;

HTH

Jonathan

[jagan]

Hi,

Try this script

LOAD

*,

If(Days < 30, '<30 Days',

     If(Days <= 60, '30-60 days',

     If(Days <= 90, '60-90 days',  '>90 days'))) AS Bucket

WHERE Flag = 1;   //

LOAD

*,

Num(SaleDate - PurchaseDate) AS Days,

If(Item <> Peek(Item), 1, 0) AS Flag

FROM DataSource

ORDER By Item, PurchaseDate, SaleDate Desc;