Announcements
Sept. 27, 10AM ET Do more with Qlik, Insight Advisor – our intelligent AI-assistant in Qlik Sense: Register
cancel
Showing results for
Did you mean:
Not applicable

Opening Balance

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:

AccountPeriod IDOpeningDebitsCreditsNet ActivityPeriod Balance
10-BEG

0.00

5,900,000.006,700,000.00-800,000.00-800,000.00
1

1-JAN

-800,000.008,500,000.00

9,800,000.00

-1,300,000.00-2,100,000.00
12-FEB-2,100,000.000.001,200,000.001,200,000.00-900,000.00
13-MAR-900,000.000.001,000,000.001,000,000.00100,000.00
14-APR100,000.00-100,000.00600,000.00500,000.00

600,000.00

20-BEG0.005,900,000.006,700,000.00-800,000.00

-800,000.00

21-JAN-800,000.008,500,000.009,800,000.00-1,300,000.00

-2,100,000.00

22-FEB-2,100,000.000.001,200,000.001,200,000.00

-900,000.00

23-MAR-900,000.000.001,000,000.001,000,000.00

100,000.00

Thank you!

1 Solution

Accepted Solutions
MVP

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

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

5 Replies
Partner - Champion III

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

MVP

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

MVP

You can use as below..

Temp:

[Period ID],

Debits,

Credits,

[Net Activity],

[Period Balance]

FROM

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

Final:

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;

MVP

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

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

Not applicable
Author

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

Community Browser