How to get beginning and ending inventory using Set Analysis?

[#1]

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

[#2]

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...