Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Anil,
With the help of Class function you can create these aging buckets easily.
Thanks and Regards,
Ankita
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
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.
Hi Jagan,
Thanks for reply. I need to do ageing for balance stock only.
Regards,
Anil
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
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;