6 Replies Latest reply: Nov 27, 2017 10:54 AM by omar bensalem RSS

    Opening and Closing Balances per month

    Angel Tomov

      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

        • Re: Opening and Closing Balances per month
          A.M. van Keep

          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;