Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.


Creating a Trial Balance Sheet in QlikView

When using QlikView, sometimes we are faced with challenges such as how do we transform an Excel spreadsheet that looks like the image below into a trial balance chart that allows us to see the monthly activity for each company and account.


Well using various functions and features of QlikView such as:

•          CrossTable Load of Excel spreadsheet

•          IsNull() function

•          RowNo() function

•          Peek() function

•          Date functions

•          Preceding load

We can create a trial balance sheet that looks like this:

Trial balance.png

Creating this chart involves loading the Excel spreadsheet into QlikView in a format that works best for us using a CrossTable load.  After sorting this data by Company Number, Account Number and Month Year, we are ready to create the Opening and Closing fields that we will need for the trial balance chart.  Using the RowNo() and Peek() functions, we are able to create the opening and closing balance fields for each account number on a monthly basis allowing the user to see what the activity was like during any given month.  Here is a snippet of what that script looks like:


You can view the step by step details of how the trial balance chart was created in this technical brief.


1 Comment
Not applicable

Nice example ! There is a big assumption however. The fact that every month has always a record even with zero values. If you had the periods in records which the case in most ERP systems, a period without transactions does not have a record with zero values.

This has to be initialized by the QV App.