Forum Discussion
Solution SageCalculating Balance on Hand
Hello:
I am trying to calculate blance on hand where stores draw down inventory based on their open date and in a separate WHINV table resides the beginning inventory. I am attaching the example file and an example of expected results. The store have an orders table alhough the WHINV is in a separate table for only begining inventroy that does not reference stores.
If possible could you also help with another measure that identifies the last date (Store Open Date) before inventory falls into the negative?
Thank you very much. File attached..https://drive.google.com/file/d/1PIpjNZeWTU_1mYRtK6N184zmQbstr5hp/view?usp=sharing
see if this is better.
10 Replies
- lbendlin
Super User
Is this a more appropriate data model?
What is "Store Open Date"? Looks like all products were depleted with their first orders.
Whitewater100Thank you for replying. Just as an example. One product ID 1 - it starts out with 185 units in the WHINV file. Store One orders 35 units 0n 1-10-2024 so the inventory would be at 150 on Jan 10 -2024. Then on 3-25-2024 the next store ID =2, orders 76 units leaving 74 units as of 3-25-2024. And it follows on lke that. The hard part(I'm rusty) for me was that the WJINV is not by store but the withdrawals for store openings are by store. The store order date key is impoertant as the WHINV is static and is prior to any withdrawls. I hope this helps explain. I tried to make a WHINV Date relationship but was unable to becasue the order table had that relationship and the order table is used more for determining what date a product goes out of stock. Thank you for looking at this problem!
- lbendlin
Yes, my bad - that should have been an inactive relationship.
So now you want to know when each product is projected to hit zero stock?
- Whitewater100
That would be awesome. Either last instock date or firsat out of stock. Either one is great. Thank you very much!!
- Whitewater100
Store Open Date is withdrawal Date. Thanks..
- lbendlin