# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor III

## Opening and Closing Balances per month

Hello guys, I attach an exemplary Excel File, and the way my QlikSense App must look like.

So Basically i have a general ledger type of structure, with accounts, departments, transaction date and the sum of the transactions

What I need to do is make a straight table ( or a pivot), which shows Opening Debit/Credit, Current Debit/Credit, and Closing Debit/Credit for the selected period.

For example if the user selects month February, the opening Debit will be The sum of all Debit transactions for January.

I have to make 2 tables, 1 with dimension Account Number,

and  a second one, which deepens the analysis allowing the user to see the Opening/Closing Debit/Credit of all the Departments in this Account Number.

-All help and advice will be highly appreciated

1 Solution

Accepted Solutions
Valued Contributor

## Re: Opening and Closing Balances per month

Hi Angel,

What you need is a cumulative calculation in your script. Below I build it for you per accountnumber,per department per month. I.e. for every month, i calculated the begin and end saldo per accountnumber and department.

I sorted the data on account, department and transactiondate (added a rowcounter because there can be multiple transactions on 1 date) .

For every account and department combination I calculated the cumulatives by peeking at the previous row. Then I tagged the last transaction per month, account and department. And based on the sum of the debits/credits I calculated the mutations in between which I  use to calculate the begin debit/credit per month. I attached the qvf.

table1:

"Account Number",

"Department",

"Transaction Type",

"Transaction Date",

Month("Transaction Date") AS Month,

YEAR("Transaction Date") AS Year,

Debit,

Credit

FROM [lib://a/OpeningClosingTest.xlsx]

(ooxml, embedded labels, table is Sheet1);

table2:

"Account Number",

"Department",

"Transaction Type",

Month,

Year,

"Transaction Date",

ROWNO() AS ROW,

Debit,

Credit,

IF(PREVIOUS("Department") = "Department" AND PREVIOUS("Account Number") = "Account Number",PEEK('Debit_cum',-1)+ Debit,Debit) AS Debit_cum,

IF(PREVIOUS("Department") = "Department" AND PREVIOUS("Account Number") = "Account Number",PEEK('Credit_cum',-1)+ Credit,Credit) AS Credit_cum

RESIDENT table1

ORDER BY "Account Number","Department","Transaction Date";

DROP TABLE table1;

"Account Number",

"Department",

Month,

Year,

SUM(Debit) AS Debit_Mutation,

SUM(Credit) AS Credit_Mutation,

MAX("Transaction Date") AS "Transaction Date",

MAX(ROW) AS ROW,

1 AS _Flag_EndMonth

RESIDENT table2

GROUP BY  "Account Number",    "Department",    Month, Year;

table:

table2:

"Account Number",

"Department",

"Transaction Type",

Month,

Year,

"Transaction Date",

Debit,

Credit,

IF(_Flag_EndMonth = 1, Debit_cum) AS Debit_EndMonth,

IF(_Flag_EndMonth = 1, Debit_cum)-Debit_Mutation AS Debit_StartMonth,

IF(_Flag_EndMonth = 1, Credit_cum) AS Credit_EndMonth,

IF(_Flag_EndMonth = 1, Credit_cum)-Credit_Mutation AS Credit_StartMonth

RESIDENT table2;

DROP TABLE table2;

6 Replies
Valued Contributor

## Re: Opening and Closing Balances per month

Hi Angel,

What you need is a cumulative calculation in your script. Below I build it for you per accountnumber,per department per month. I.e. for every month, i calculated the begin and end saldo per accountnumber and department.

I sorted the data on account, department and transactiondate (added a rowcounter because there can be multiple transactions on 1 date) .

For every account and department combination I calculated the cumulatives by peeking at the previous row. Then I tagged the last transaction per month, account and department. And based on the sum of the debits/credits I calculated the mutations in between which I  use to calculate the begin debit/credit per month. I attached the qvf.

table1:

"Account Number",

"Department",

"Transaction Type",

"Transaction Date",

Month("Transaction Date") AS Month,

YEAR("Transaction Date") AS Year,

Debit,

Credit

FROM [lib://a/OpeningClosingTest.xlsx]

(ooxml, embedded labels, table is Sheet1);

table2:

"Account Number",

"Department",

"Transaction Type",

Month,

Year,

"Transaction Date",

ROWNO() AS ROW,

Debit,

Credit,

IF(PREVIOUS("Department") = "Department" AND PREVIOUS("Account Number") = "Account Number",PEEK('Debit_cum',-1)+ Debit,Debit) AS Debit_cum,

IF(PREVIOUS("Department") = "Department" AND PREVIOUS("Account Number") = "Account Number",PEEK('Credit_cum',-1)+ Credit,Credit) AS Credit_cum

RESIDENT table1

ORDER BY "Account Number","Department","Transaction Date";

DROP TABLE table1;

"Account Number",

"Department",

Month,

Year,

SUM(Debit) AS Debit_Mutation,

SUM(Credit) AS Credit_Mutation,

MAX("Transaction Date") AS "Transaction Date",

MAX(ROW) AS ROW,

1 AS _Flag_EndMonth

RESIDENT table2

GROUP BY  "Account Number",    "Department",    Month, Year;

table:

table2:

"Account Number",

"Department",

"Transaction Type",

Month,

Year,

"Transaction Date",

Debit,

Credit,

IF(_Flag_EndMonth = 1, Debit_cum) AS Debit_EndMonth,

IF(_Flag_EndMonth = 1, Debit_cum)-Debit_Mutation AS Debit_StartMonth,

IF(_Flag_EndMonth = 1, Credit_cum) AS Credit_EndMonth,

IF(_Flag_EndMonth = 1, Credit_cum)-Credit_Mutation AS Credit_StartMonth

RESIDENT table2;

DROP TABLE table2;

Contributor III

## Re: Opening and Closing Balances per month

Some of the values for example Account 501, doesn't have associated Department, and it shows me null values each time.

In my original file I got more than 150 accounts ... Some of them have departments, some do not..

How to proceed?

Valued Contributor

## Re: Opening and Closing Balances per month

Try IF(ISNULL(Department)=0,Department,'No Department') AS Department

or IF(LEN(Department)>0,Department,'No Department') AS Department

Contributor III

## Re: Opening and Closing Balances per month

where, on the last loaded table?

Valued Contributor

## Re: Opening and Closing Balances per month

table1:

"Account Number",

"Department",

IF(ISNULL(Department)=0,Department,'No Department') AS Department_new1,

IF(LEN(Department)>0,Department,'No Department') AS Department_new2,

"Transaction Type",

"Transaction Date",

Month("Transaction Date") AS Month,

YEAR("Transaction Date") AS Year,

Debit,

Credit

FROM [lib://a/OpeningClosingTest.xlsx]

(ooxml, embedded labels, table is Sheet1);

Esteemed Contributor

## Re: Opening and Closing Balances per month

I'd suggest:

IF(LEN (trim( (Department) )>0,Department,'No Department') AS Department_new2,