Opening and Closing Balances per month

[tomovangel]

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

[avkeep01 · Accepted solution]

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:

LOAD

    "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:

NOCONCATENATE LOAD

"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;

  LEFT JOIN (table2) LOAD

"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:

NOCONCATENATE LOAD

"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;

[tomovangel]

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?

[avkeep01]

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

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

[tomovangel]

where, on the last loaded table?

[avkeep01]

table1:

LOAD

    "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);

[OmarBenSalem]

I'd suggest:

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

instead of:

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