13 Replies Latest reply: Jan 24, 2018 6:55 AM by kk kk

# Inventory aging buckets

Dears,

I'm working on inventory aging and i have been trying to distribute QTY & cost  based on receiving dates.

the problem is :

lets assume that i have 19 pieces of item A as on hand and on the other hand i have received this item on mulitpule dates & QTY (Total recievd 42 ,and 23 have been sold before the selected date  )

the user will select 12/dec/2017 as of that date, i have 19 qty out of 42 received From item A , and this item has been received on the following dates

28/Nov/20171330-601 of 19
25/Nov/20171

36

0-601 of 19
14/Jun/201710200180-36510 of 19
13/Jun/201713201180-3657 of 19

is it possible to be done in qlik.

• ###### Re: Inventory aging buckets

Can you articulate better as to what you are trying to achieve?

I am unable to comprehend the problem here.

• ###### Re: Inventory aging buckets

let me try this again.

I am working on inventory aging where i need to distribute stock items into buckets based on two dates

first one will be selected by the user(31/DEC/2017) the second one based on the multi received dates.

now for item A i have 19 as on hand and initially there are 42  which have been received (23 have been sold)

now to calculate the aging bucket i need to go check the receiving dates and QTY received to distribute these 19 QTY.

check the dates on the below Picture.

so my on hand QTY is 19 , these 19 should be distributed into bucket based on the receiving dates  as the below picture

i hope this would make sense to everyone

• ###### Re: Inventory aging buckets

If you are doing in the front end then a calculated dimension in master items or the chart itself should handle the bucketing something like

IF(Difference between days < 60, '0-60'

IF(Difference between days < 180, '61-179'

IF(Difference between days < 366, '180-365', 'N/A')))

Regards

Andy

• ###### Re: Inventory aging buckets

this would help if im only looking at one receiving date.the problem is the on hand qty should be distributed based on two factor Receiving QTY And Receiving Date.

• ###### Re: Inventory aging buckets

Would you be able to share some data in its raw form in an Excel file to see what you have....

• ###### Re: Inventory aging buckets

allow me to further explain the case im having

i have merged three reports (total stock,booked,damage) in order to calculate the sound stock which is equal to

Total Stock-(Booked + Damage)

now what im trying to achieve is to distribute the sound stock (cost and Qty) into aging buckets.

these aging buckets is based on two dates

first= select date by the user

Second= Multiple receiving Dates

lets take the below image as example:

now the selected date is 12/31/2017, and the below image show the receiving dates for that item

Note:we should only consider receiving dates which are less or equal to the selected Date

now lets look at how the Sound Qty and Cost  Should be distributed

from the 1st image we can see that we have 99 as sound stock of that item, these 99 Qty should be distributed as below

the reason for doing this is to achieve the below

PFA

• ###### Re: Inventory aging buckets

stevedark can you please have a look on the above

• ###### Re: Inventory aging buckets

Hi Khaled,

As far as I can see there is not a 100% straight forward answer to this problem.

Just to confirm; you want to find the current on hand stock for each object, and then work out the age of stock that you have on hand, based on the assumption that the stock is moved on a "First in First out" basis?

I'm assuming that no user selections in the front end is going to impact on this in any way shape or form?

As such, I would look at dealing at this in the load script, to make the front end calculations easier.

To do this, load the table from which you can get stock on hand for each product, then load this into a MAPPING TABLE (you may or may not need a group by for this).

Map_ProdOnHand:

SKU,

OnHand

RESIDENT CurrentStock;

Now map that onto the deliveries table.  You will need to parse this table twice, so that you can apply the map and apply the order by.  So, the first pass would be:

tmpDeliveries:

SKU,

DelDate,

Floor(DelDate - Today()) as DaysAgo,

ApplyMap('Map_ProdOnHand', SKU, 0) as TotalOnHand

etc.

You can then load the rows in product / delivery order and check the cumulative stock, with a peek;

Deliveries:

*,
if(IsCurrentStock, DeliveryAgedGroup, null()) as CurrentStockAgedGroup,

if(IsCurrentStock, Qty, null()) as CurrentStockDeliveredQty

;

*,

if(PrevQty <= TotalOnHand, True, False) as IsCurrentStock

;

SKU,

DelDate,

DaysAgo,

if(DaysAgo<= 60, Dual('0-60', 0),

if(DaysAgo <= 180, Dual('61-180', 61),

etc..

))))))))  as DeliveryAgedGroup,

TotalOnHand,

Qty,

if(peek(SKU, -1) = SKU, CumQty, 0) as PrevQty,

Qty + if(peek(SKU, -1) = SKU, CumQty, 0) as CumQty,

etc.

RESIDENT tmpDeliveriers

ORDER BY SKU, DelDate DESC

;

DROP TABLE Deliveries;

You may want to then drop some or all of the work in progress fields, such as TotalOnHand, PrevQty and CumQty as these will cause double counting if someone tries to tot them up in the front end.  They could be useful for debugging though.

Once you have the CurrentStockAgedGroup and CurrentStockDeliveredQty fields created the build of the front end should be straight forward.

If you need to get it accurate regarding qty in current stock you may need to have some of one delivery in CurrentStockDeliveredQty and some not.  You can do this by comparing TotalOnHand and CumQty and taking off the difference from the delivery which takes you over the current on hand.

Hopefully I've understood what you are after and have explained the solution well enough.

All the code has been typed directly into Qlik Community - so there may well be some syntax issues, hopefully it points you in the right direction though.

Cheers,

Steve

• ###### Re: Inventory aging buckets

Thanks Steve for your response and valuable info.

"Just to confirm; you want to find the current on hand stock for each object, and then work out the age of stock that you have on hand, based on the assumption that the stock is moved on a "First in First out" basis?"

Yes you right the stock is on the basis of "First In First Out"

"I'm assuming that no user selections in the front end is going to impact on this in any way shape or form?"

Dealing with it on the back end would be the optimal solution, but the requirements is to allow the user to select the date instead of Today() therefore this needs to be moved to the front end in order to calculate the buckets accordingly.

The problem is that one item can have different aging buckets depending on the Sound stock, receiving dates & Receiving QTY.

lets take the below example:

assuming the user has selected :31/12/2017

Item Code:

 03-21464-MCO-00-71

Sound QTY:42

now these QTY should be distributed as below :

the above is achieved using PL-SQL with the use of cursor and for loops after taking date as an input from the user.

the goal is to achieve the below

• ###### Re: Inventory aging buckets

Allowing the user to pick the date will not make this easy at all.  You will need to be using the Above statement, and probably some Aggr statements.

How many days back do you want to allow the user to go?  You may be able to do something in the load script if it is just a few days rather than them being able to pick any date.

Steve

• ###### Re: Inventory aging buckets

what i understand from you that if the user is given the leverage to pick any date will make this hard to achieve, but if we are looking at as of current date it can be done ??

• ###### Re: Inventory aging buckets

Exactly.  If it is based on current date, as at time of data reload, then all the elements you should need are in the code I provided above.  If it is dynamic in the front end then if it can be coded for it will perform very badly.

• ###### Re: Inventory aging buckets

Thanks alot Steve for your feedback. i will keep on working on this case and will update this thread once its all settled down