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
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
Desktop.rar 3.9 MB
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).
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:
Floor(DelDate - Today()) as DaysAgo,
ApplyMap('Map_ProdOnHand', SKU, 0) as TotalOnHand
You can then load the rows in product / delivery order and check the cumulative stock, with a peek;
if(IsCurrentStock, DeliveryAgedGroup, null()) as CurrentStockAgedGroup,
if(IsCurrentStock, Qty, null()) as CurrentStockDeliveredQty
if(PrevQty <= TotalOnHand, True, False) as IsCurrentStock
if(DaysAgo<= 60, Dual('0-60', 0),
if(DaysAgo <= 180, Dual('61-180', 61),
)))))))) as DeliveryAgedGroup,
if(peek(SKU, -1) = SKU, CumQty, 0) as PrevQty,
Qty + if(peek(SKU, -1) = SKU, CumQty, 0) as CumQty,
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.
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
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
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.