topic Re: Re: Calculting Stock on Hand For Six month in QlikView

Calculting Stock on Hand For Six month

Tue, 25 Feb 2014 08:30:44 GMT

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

Re: Calculting Stock on Hand For Six month

Tue, 25 Feb 2014 10:02:54 GMT

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

Re: Calculting Stock on Hand For Six month

Tue, 25 Feb 2014 10:40:22 GMT

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

Re: Re: Calculting Stock on Hand For Six month

maxgro — Tue, 25 Feb 2014 10:40:53 GMT

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;

Re: Calculting Stock on Hand For Six month

Tue, 25 Feb 2014 11:38:06 GMT

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 ;

Re: Calculting Stock on Hand For Six month

preminqlik — Tue, 25 Feb 2014 13:54:35 GMT

Hi there PFA

Re: Re: Calculting Stock on Hand For Six month

Tue, 25 Feb 2014 14:24:16 GMT

hi Prem

thanks for replay ,

i got answer this Way

Please find attchment

Re: Calculting Stock on Hand For Six month

preminqlik — Wed, 26 Feb 2014 05:44:06 GMT

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

Re: Re: Calculting Stock on Hand For Six month

Thu, 27 Feb 2014 06:42:39 GMT

hi Anuez Can u help me out

For Calculating Days of supply for

i have given logic in qvw

Re: Re: Re: Calculting Stock on Hand For Six month

Thu, 27 Feb 2014 07:09:36 GMT

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