Calculate Opening Balance in Load script

[rajat2392]

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

[Oleg_Troyansky]

Hi Rajat,

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

best,

Oleg Troyansky

[Kushal_Chawda · Accepted solution]

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;

[miguelbraga]

Hey Rajat,

See my qvw

Regards,

MB

[sunny_talwar]

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;

[petter]

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

  .......;

[kashifsaifi]

Hi There,

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