Say that I have these tables:
SELLING CALENDAR: (Fiscal Calendar)
|MONTH YEAR||START DATE||END DATE|
ITEM INQUIRY: (This is the daily inquiry of available Quantity)
PURCHASE ORDER PLAN: (This is only plan quantity)
|PLAN DATE||ITEM||ORDER PLAN|
RECEIVED ORDER: (This will be based on order plan (sometimes delivered are less, sometimes more than the ordered - but this is ok, dont mind the QTY)
|RECEIVED DATE||ITEM||RECEIVED QTY|
ACTUAL SALES: (This is actual sales)
|INVOICE DATE||ITEM||INVOICE QTY|
BELOW IS WHAT I WANTED TO HAVE:
Beginning Inventory: FORMULA: Last Month Ending Inventory or Last Month Selling Date in Item Inquiry
-but what I want here is that, as much as possible there's no use of above() functions, I want to put exactly what's last month ending inventory formula, so that when I select only one month, I still get the actual beginning inventory. I don't want the BEGINNING INVENTORY turns 0 when only one month is selected.
Say for example: when month is January, the result should get
DECEMBER BEGINNING INVENTORY - DECEMBER ACTUAL SALES + NOVEMBER RECEIVED ORDER
- so that the result will be fix and not change to ZERO when only one month is selected, but if there's no way to get that, then let's straight forward, BEGINNING INVENTORY will be LAST MONTH ENDING INVENTORY
Beginning Inventory is the available balance (see item inquiry) for last ending date of sales (see selling calendar)
Is it applicable here to apply set analysis?
Ending Inventory: FORMULA: This Month Beginning Inventory - Actual Sales + This Month Received Order
Actual Sales: FORMULA: This Month Actual Sales
Purchase Order Plan: FORMULA: This Month Order Plan
Received Order: FORMULA: This Monthh Received Order
|Purchase Order Plan||50||40||45|
January Beginning is: December ending or Last selling date of December
January Ending is: Jan Beginning - Jan Actual Sales + Jan Received Order
55 - 10 + 45 = 90
Februay Beginning is: Jan Ending Beginning or should it be possible to apply the full formula of getting JANUARY ending?
So the question is: how can I get Feb as 90 without using above() function? is it possible? Can I able to comeup this? Or please help, either way, as long as this can be resolve.