return the previous month amount in current month

[#1]

Hi,

I want to be able to create a rolling stock on hand report based on a stock on hand report updated each day, adding units in transit to our warehouse from our head office overseas (based on the expected receipt date) minus forecast orders going to customers to create a SOH at month end. I then want to include future months where the following months Open SOH (stock on hand) equals the previous months Closing SOH.

I can assign the current month Open SOH using a today() function in the stock on hand load but I cannot seem to get next months (Nov-14) Open SOH to be Oct-14 Closing SOH. E.g. for Product1 we will end Oct-14 with 428 units in the warehouse. I then want Nov-14 Open SOH to be 428 so when we take off the forecast customer orders of 160 units in November the Closing SOH says 268 units not 112.

I have tried a number of ways to do this without any luck. I included a dimension called PeriodChrono as each month is given an accending number and the report knows that 76 is the current month if that helps.

Can anyone suggest any solutions?

PeriodName	Oct-14	Oct-14	Oct-14	Oct-14	Nov-14	Nov-14	Nov-14	Nov-14
PeriodChrono	76	76	76	76	77	77	77	77
SKU	Open SOH	Units in Transit	Forecast Orders	Closing SOH	Open SOH	Units in Transit	Forecast Orders	Closing SOH
Product1	272	576	420	428	0	0	160	112
Product2	586	1,082	1,060	608	0	0	400	186
Product3	552	688	668	572	0	0	260	292
Product4	3,819	0	6,758	-2,939	0	0	2,200	1,619
Product5	1,430	0	0	1,430	0	0	0	1,430
Product6	868	0	1,719	-851	0	0	520	348
Product7	957	616	188	1,385	0	0	957
Product8	495	580	568	507	0	0	220	275
Product9	704	992	952	744	0	0	368	336
Product10	1,030	412	0	1,442	-	-	-	-

Thanks

David

[anbu1984]

You can try like this in script

Temp:

Load *,Match(SKU,'Open SOH','Units in Transit','Forecast Orders','Closing SOH') As Ord Inline [

PeriodName,PeriodChrono,SKU,Product,Amount

Oct-14,76,Open SOH,Product1,272

Oct-14,76,Units in Transit,Product1,576

Oct-14,76,Forecast Orders,Product1,420

Oct-14,76,Closing SOH,Product1,428

Nov-14,77,Open SOH,Product1,0

Nov-14,77,Units in Transit,Product1,0

Nov-14,77,Forecast Orders,Product1,160

Nov-14,77,Closing SOH,Product1,112

Oct-14,76,Open SOH,Product2,586

Oct-14,76,Units in Transit,Product2,1082

Oct-14,76,Forecast Orders,Product2,1060

Oct-14,76,Closing SOH,Product2,608

Nov-14,77,Open SOH,Product2,0

Nov-14,77,Units in Transit,Product2,0

Nov-14,77,Forecast Orders,Product2,400

Nov-14,77,Closing SOH,Product2,186 ];

NoConcatenate

Final:

Load PeriodName,PeriodChrono,SKU,Product,If(Product=Previous(Product) And PeriodChrono = Previous(PeriodChrono)+1 And SKU='Open SOH' And Amount = 0, Previous(Amount),Amount) As Amount

Resident Temp Order by Product,PeriodChrono,Ord;

Drop Table Temp;