Calculting Stock on Hand For Six month

[#1]

hi all

I have Data In which i want to calculate Stock in Hand in script

by using formula

Stock On Hand =SOH=Intial_Stock+Recipt+production - Forecast

and i have Initial stock of one Day

for from Day2  next initial  stock will be stock ON hand of previous day

PFA

Thanks in Advance

[#2]

hi all Am getting Problem picking Stock value of last day as Today's initial Stock

[#3]

I would do that:

//only 90 lines with dates into the file
// we need to sort the file by product/location so that the dates follow each other for a single product/location
// we merge the two files to facilitate the sort afterwards

Temp:
First 90
LOAD Product & '_' & Location as PL,
p_Date,
Intial_Stock,
Forecast,
Recipt,
Production
FROM
[108520_STOCKONHAND.xlsx]
(ooxml, embedded labels, table is Sheet1);

// we can get back the two fields Product and Location
// we order by date and this pseudo field PL so that the dates follow each other and we can use the peek() function
Data:
NoConcatenate
LOAD subfield(PL, '_', 1) as Product,
subfield(PL, '_', 2) as Location,
p_Date,
Forecast,
Recipt,
Production,
// assumption: first date is populated so that we do not take a stock from another Product/location
if (isnull(Intial_Stock), peek('SOH'), Intial_Stock) as Intial_Stock,
Rangesum(if (isnull(Intial_Stock), peek('SOH'), Intial_Stock), Recipt, Production)-alt(Forecast,0) as SOH
Resident Temp
Order by PL, p_Date;

Drop table Temp;

Fabrice

[maxgro · Accepted solution]

hope I understand your question

here the script, see attachment

Directory;

Source:

LOAD Product,

     Location,

     //date(date#(p_Date, 'DD/MM/YYYY')) as p_Date,

     p_Date,

     Intial_Stock,

     Forecast,

     Recipt,

     Production,

     [Soh in Excel Ihave calculted=]

FROM

STOCKONHAND.xlsx

(ooxml, embedded labels, table is Sheet1)

Where len(p_Date) > 0

;

Table:

load *,

  if(peek(Product)<>Product or peek(Location) <> Location,

  Intial_Stock+Recipt+Production -Forecast,

  peek(SOH)+Recipt+Production -Forecast) as SOH

Resident Source

order by Product,

     Location,

     p_Date;

drop table Source;

[#5]

hi Massimo

i have done like this

Data:

LOAD Product,

     Location,

     p_Date,

     Intial_Stock,

     Forecast,

     Recipt,

     Production

     //,AutoNumber(Product&'-'&Location&'-'&p_Date) as ID

     //,Intial_Stock+Recipt+Production -Forecast as ISOH

FROM

STOCKONHAND.xlsx

(ooxml, embedded labels, table is Sheet2);

Data1:

LOAD *,If(IsNull(Intial_Stock),Peek(SOH)+Recipt+Production -Forecast,Intial_Stock+Recipt+Production -Forecast) as SOH

Resident Data

Order by Product,Location,p_Date ;

[preminqlik]

Hi there PFA

[#7]

hi Prem

thanks for replay ,

i got answer this Way

Please find attchment

[preminqlik]

Hey seems to be good..thanks for sharing...

[#9]

hi Anuez Can u help me out

For Calculating Days of supply for

i have given logic in qvw

[#10]

hi Massimo

Can u please help me out in Calculating Days Of Supply

i given loogic in Qvw file

Thanks in advance

Calculating Days Of Supply Depends Sell Forcast