Opening Balance

[#1]

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!

[alexandros17]

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

[MK_QSL]

What is your Net Activity?

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

[MK_QSL]

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;

[maxgro · Accepted solution]

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

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

[#6]

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