topic Re: Calculate Opening Balance in Load script in QlikView

Calculate Opening Balance in Load script

rajat2392 — Mon, 30 May 2016 14:45:56 GMT

Hi Everyone,

I am stuck in a situation for calculating Closing Stock month on month in load script.

Below is the scenario.

Year	Month	Sales	Production
2016	Jan	1000	1500
2016	Feb	800	1300
2016	Mar	1000	1400
2016	Apr	900	1500
2016	May	500	1400

Stock in hand at the start of the year is 0.

I need the below output.

Year	Month	Sales	Production	Opening
2016	Jan	1000	1500	0
2016	Feb	800	1300	500
2016	Mar	1000	1400	1000
2016	Apr	900	1500	1400
2016	May	500	1400	2000

Please some one help me with the script.

I tried a lot but it isn't happening.

Thanks in Advance

Rajat Arora

Re: Calculate Opening Balance in Load script

Oleg_Troyansky — Mon, 30 May 2016 15:06:30 GMT

Hi Rajat,

I'm describing exactly this process in my book QlikView Your Business - check it out!

best,

Oleg Troyansky

Re: Calculate Opening Balance in Load script

Kushal_Chawda — Mon, 30 May 2016 15:18:36 GMT

Data:
LOAD *,
Date(Date#(Year&Month,'YYYYMMM'),'YYYYMM') as MonthYear;
LOAD Year,
Month,
Sales,
Production
FROM
[https://community.qlik.com/thread/218884]
(html, codepage is 1252, embedded labels, table is @1);

New:
LOAD *,
if(RowNo()=1,0, RangeSum((Previous(Production)-Previous(Sales)),Peek('Opening'))) as Opening
Resident Data
Order By MonthYear;

DROP Table Data;

Re: Calculate Opening Balance in Load script

miguelbraga — Mon, 30 May 2016 15:20:19 GMT

Hey Rajat,

See my qvw

Regards,

Re: Calculate Opening Balance in Load script

sunny_talwar — Mon, 30 May 2016 15:25:40 GMT

May be this:

Table:

LOAD *,

Date(Date#(Month & '-' & Year, 'MMM-YYYY'), 'MMM-YYYY') as MonthYear;

LOAD Year,

Month,

Sales,

Production

FROM

[https://community.qlik.com/thread/218884]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

If(RowNo() = 1, 0, RangeSum(Peek('Opening'), Peek('Production'), -Peek('Sales'))) as Opening

Resident Table

Order By MonthYear;

DROP Table Table;

Re: Calculate Opening Balance in Load script

petter — Mon, 30 May 2016 15:29:13 GMT

If your source records are already sorted by date and aggregated monthly this load statement should do the calculation for you:

LOAD

Year, Month, Sales, Production,

If ( RecNo () = 1, 0 , Peek('Opening')-Peek ('Sales')+Peek ('Production') ) AS Opening

FROM

.......;

Re: Calculate Opening Balance in Load script

kashifsaifi — Tue, 22 Oct 2019 19:41:55 GMT

Hi There,

What if I want to break it down by year. Meaning, for 2017 onwards, the opening balance should start with 0.