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
.
Trial Balance.xlsx
file. Name it QlikSenseCookBook _TB
.TrialBalance.xlsx
file 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);
Month
and the MonthYear
field 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;
Opening Balance
and Closing Balance
fields using the Peek()
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;
" "
). 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, the vMaxMonth
variable is highlighted in purple. In this case, the user must make sure that a proper format of double quotes is in place.Opening
:Sum({<Month={"<=$(vMaxMonth)"}>} Opening)
Amount
:Sum({<Month={"<=$(vMaxMonth)"}>} Amount)
Closing
:Sum({<Month={"<=$(vMaxMonth)"}>} Closing)
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.