Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Say that I have these tables:
SELLING CALENDAR: (Fiscal Calendar)
MONTH YEAR | START DATE | END DATE |
---|---|---|
NOV-2011 | 10/26/2011 | 11/25/2011 |
DEC-2011 | 11/26/2011 | 12/25/2011 |
JAN-2012 | 12/26/2011 | 1/25/2012 |
FEB-2012 | 1/26/2012 | 2/25/2012 |
MAR-2012 | 2/26/2012 | 3/25/2012 |
ITEM INQUIRY: (This is the daily inquiry of available Quantity)
DATE INQUIRY | ITEM | AVAILABLE |
---|---|---|
12/25/2011 | ITEM A | 55 |
1/25/2012 | ITEM A | 90 |
2/25/2012 | ITEM A | 107 |
3/25/2012 | ITEM A | 141 |
PURCHASE ORDER PLAN: (This is only plan quantity)
PLAN DATE | ITEM | ORDER PLAN |
---|---|---|
1/20/2012 | ITEM A | 50 |
2/15/2012 | ITEM A | 40 |
3/10/2012 | ITEM A | 45 |
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 |
---|---|---|
11/25/2011 | ITEM A | 10 |
12/24/2011 | ITEM A | 30 |
1/20/2012 | ITEM A | 45 |
2/16/2012 | ITEM A | 40 |
3/11/2012 | ITEM A | 50 |
ACTUAL SALES: (This is actual sales)
INVOICE DATE | ITEM | INVOICE QTY |
---|---|---|
12/23/2011 | ITEM A | 5 |
1/25/2012 | ITEM A | 10 |
1/30/2012 | ITEM A | 23 |
3/20/2012 | ITEM A | 16 |
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
JAN-2012 | FEB-2012 | MAR-2012 | |
---|---|---|---|
Beginning Inventory | 55 | 90 | 107 |
Actual Sales | 10 | 23 | 16 |
Purchase Order Plan | 50 | 40 | 45 |
Received Order | 45 | 40 | 50 |
Ending Inventory | 90 | 107 | 141 |
January Beginning is: December ending or Last selling date of December
55
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.
Thanks,
Bill
Hi
My point of view is that is not an issue to be solved using set analysis. I would try to solve it in the script and save the results directly to a qvd file. For example:
Lets say that you need to build the rolling qty per product while you read the initial table you should build
an accumulation key like the following one in order to build break levels:
Load company & '|' & product & '|' & Year AS AccumulationKey, PeriodQty as Qty_trans...
Then you sould reference to that resident table and build opening and closing figures with the following technique:
FinalStock:
LOAD
product,
Year, Month, AccumulationKey, Qty_trans,
if(rowno() = 1 OR AccumulationKey <> peek(AccumulationKey), 0, peek(ClosingQty)) AS OpeningQty,
if(rowno() = 1 OR AccumulationKey <> peek(AccumulationKey), Qty_trans, peek(ClosingQty) + Qty_trans) AS ClosingQty,
if(rowno() = 1 OR AccumulationKey <> peek(AccumulationKey), Qty_Weight, peek(ClosingWeight) + Qty_Weight) AS ClosingWeight,
RESIDENT
TempSum
;
On the above code for the 1st row the opening amount is 0 and whenever there is a break level
on accoumulation key the opening amount is set to 0 again. Into the same level period qty is accumulates to closing quantity which becomes the opening qty of the next month.
I hope it helps...