A Trial Balance sheet captures the activity across different accounts of a company with regards to the opening and closing balances. The following recipe focuses on creation of a trial balance sheet in Qlik Sense.
The recipe will make use of the TrialBalance.xlsx file, which can be downloaded from the Packt Publishing website.
Store the file on your system at the following location C:/QlikSense.
- Create a folder connection to the
Trial Balance.xlsxfile. Name itQlikSenseCookBook _TB. - Load the data from the
TrialBalance.xlsxfile in the Qlik Sense file. We need to make use of the cross table functionality to load the data in a proper format:Let vMaxMonth=Max(Month); TrialBalancetemp: CrossTable(Month, Amount, 4) LOAD [Company Number], [Account Number], [Year], Forwarded, [January], [February], [March], [April], [May], [June], [July], [August], [September], [October], [November], [December] FROM [lib://QlikSenseCookBook_TB/Trial Balance.xlsx] (ooxml, embedded labels, table is Sheet1); - Next, we will generate the
Monthand theMonthYearfield in a resident load. Copy and paste the following script:TrialBalancetemp1: NoConcatenate LOAD [Company Number], [Account Number], Forwarded, Year, Month(Date#(Month,'MMM')) as Month, Date(MakeDate(Year, Month(Date#(Month,'MMM'))), 'MMM YYYY') as MonthYear, Amount Resident TrialBalancetemp; DROP Table TrialBalancetemp;
- The final step is to create the
Opening BalanceandClosing Balancefields using thePeek()function. Copy and paste the following script in the editor:TrialBalance: NoConcatenate LOAD CompanyAccountKey, [Company Number], [Account Number], MonthYear, Year, Month, Amount, if(Rowno() = 1 OR CompanyAccountKey <> Peek(CompanyAccountKey), Forwarded, Peek(Closing)) as Opening, if(Rowno() = 1 OR CompanyAccountKey <> Peek(CompanyAccountKey), Forwarded + Amount, Peek(Closing) + Amount) as Closing ; NoConcatenate LOAD [Company Number] & '_' & [Account Number] as CompanyAccountKey, [Company Number], [Account Number], Year, Month, MonthYear, Forwarded, Amount Resident TrialBalancetemp1 Order By [Company Number], [Account Number], MonthYear; DROP Table TrialBalancetemp1; - Load the data and save the file. Open App overview by clicking on the Navigation dropdown
at the top-left corner. - Add the Table object to the sheet.
- Add MonthYear, Company Number, and Account Number as dimensions.
- Next, we will add the expressions for measures. We specify a range of months in the set analysis expression. When we define the range, it is enclosed within double quotes (
" "). If you try to copy this expression and paste it in the Qlik Sense expression editor, sometimes the double quotes are not copied in the correct format. If the format for the quotes is incorrect, thevMaxMonthvariable is highlighted in purple. In this case, the user must make sure that a proper format of double quotes is in place. - Add the first expression to the table and label it
Opening:Sum({<Month={"<=$(vMaxMonth)"}>} Opening) - Add the second expression to the table and label it
Amount:Sum({<Month={"<=$(vMaxMonth)"}>} Amount) - Add the third expression to the table and label it
Closing:Sum({<Month={"<=$(vMaxMonth)"}>} Closing) - Under Sorting, promote Account Number to the top and set the sort order as numerically ascending.
- Promote Company Number to the second position in sorting and set the sort order as numerically ascending.
- The final table report will look like this:
The script uses a rowno() function and a Peek() function to calculate the Opening and Closing balances.
The rowno() function determines the position of the current row. If we are at the first row, then the Forwarded Amount is taken as the opening balance. If the company and account have changed, then we use the Peek() function to determine the previous closing balance, which is taken as the opening balance.
Similarly, if we are at the first row, then the Forwarded Amount + Amount added for the particular month, is taken as the closing balance. If the company and account have changed, then we use the Peek() function to determine the previous closing balance and add this value to the amount to get the final closing balance.