Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kaldubai
Creator
Creator

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

Received dateQTY ReceivedSelected date -Recv DateBucketsOn Hand Qty
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

agign.PNG

is it possible to be done in qlik.

15 Replies
siddharth_s3
Partner - Creator II
Partner - Creator II

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

I am unable to comprehend the problem here.

kaldubai
Creator
Creator
Author

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.

Receiving Dates.PNG

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

Capture.PNG

i hope this would make sense to everyone

ogster1974
Partner - Master II
Partner - Master II

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

kaldubai
Creator
Creator
Author

thanks andy for your replay

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.



sunny_talwar

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

kaldubai
Creator
Creator
Author

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:

Kuka item.PNG

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


Receving Date.PNG

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

Qty Distribuation.PNG

the reason for doing this is to achieve the below

Aging buckets.PNG

PFA

kaldubai
Creator
Creator
Author

stevedark‌ can you please have a look on the above

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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:

MAPPING LOAD

  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:

LOAD
   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:

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

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

     ;

LOAD
   *,

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

   ;

LOAD

   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

kaldubai
Creator
Creator
Author

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 :

03-21464-MCO-00-71.PNG

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

Aging buckets.PNG