topic Re: Stock ageing in QlikView

Stock ageing

anil2185 — Tue, 14 Apr 2015 04:56:01 GMT

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

Re: Stock ageing

ankitaag — Tue, 14 Apr 2015 05:58:19 GMT

Hi Anil,

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

Thanks and Regards,

Ankita

Re: Stock ageing

anil2185 — Tue, 14 Apr 2015 06:01:49 GMT

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

Re: Stock ageing

jagan — Tue, 14 Apr 2015 06:36:25 GMT

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.

Re: Stock ageing

anil2185 — Tue, 14 Apr 2015 06:48:44 GMT

Hi Jagan,

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

Regards,

Anil

Re: Stock ageing

jonathandienst — Tue, 14 Apr 2015 06:52:29 GMT

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

Re: Stock ageing

jagan — Tue, 14 Apr 2015 07:26:06 GMT

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;