topic Opening Balance in QlikView

Opening Balance

Wed, 12 Mar 2014 20:06:21 GMT

I am trying to find an expression that will allow me to calculate the Opening balance of an account by period. There are multiple accounts in the table and the layout is like the example below.

For Periods 1-12, I need to pull in the prior period's Period Balance as the current period's Opening balance(i.e., for the Opening balance of February, we will use the Period Balance for January). The Opening balance for Periods 1-12 is different than Period 0, which will use the ending balance of the prior fiscal year.

For example:

Account	Period ID	Opening	Debits	Credits	Net Activity	Period Balance
1	0-BEG	0.00	5,900,000.00	6,700,000.00	-800,000.00	-800,000.00
1	1-JAN	-800,000.00	8,500,000.00	9,800,000.00	-1,300,000.00	-2,100,000.00
1	2-FEB	-2,100,000.00	0.00	1,200,000.00	1,200,000.00	-900,000.00
1	3-MAR	-900,000.00	0.00	1,000,000.00	1,000,000.00	100,000.00
1	4-APR	100,000.00	-100,000.00	600,000.00	500,000.00	600,000.00
2	0-BEG	0.00	5,900,000.00	6,700,000.00	-800,000.00	-800,000.00
2	1-JAN	-800,000.00	8,500,000.00	9,800,000.00	-1,300,000.00	-2,100,000.00
2	2-FEB	-2,100,000.00	0.00	1,200,000.00	1,200,000.00	-900,000.00
2	3-MAR	-900,000.00	0.00	1,000,000.00	1,000,000.00	100,000.00

Thank you!

Re: Opening Balance

alexandros17 — Wed, 12 Mar 2014 20:10:11 GMT

Look at Above, Below functions, they fit for you.

Re: Opening Balance

MK_QSL — Wed, 12 Mar 2014 20:18:09 GMT

What is your Net Activity?

if it is Credits-Debits or Debits-Credits... looking wrong in your table..

Re: Opening Balance

MK_QSL — Wed, 12 Mar 2014 20:25:29 GMT

You can use as below..

Temp:

LOAD Account,

[Period ID],

Debits,

Credits,

[Net Activity],

[Period Balance]

FROM

[http://community.qlik.com/thread/110456]

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

Final:

Load

Account,

[Period ID],

Debits,

Credits,

Credits-Debits as [Net Activity],

IF(Account = Previous(Account), RangeSum((Credits-Debits),Peek('Balance')),Credits-Debits) as Balance,

IF(Account = Previous(Account), Previous(Credits)-Previous(Debits),0) as Opening

Resident Temp

Order By Account, [Period ID];

Drop Table Temp;

Re: Opening Balance

maxgro — Wed, 12 Mar 2014 20:47:42 GMT

in chart with Account andPeriod ID as dimension the expression for Opening I think is

rangesum(Above( sum([Net Activity]) , 1, rowno()))

Re: Opening Balance

Wed, 12 Mar 2014 22:06:39 GMT

The table is an example of the formatting. The numbers aren't real.

Re: Opening Balance

Phan_Anh_Son — Sun, 07 Jul 2024 16:23:20 GMT

I tried but it doesn't work, please help me get the Opening according to Account.

Thanks.