1. Create a folder connection to the Trial Balance.xlsx file. Name it QlikSenseCookBook _TB.
2. Load the data from the 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); 

3. Next, we will generate the 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; 

4. The final step is to create the 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; 

5. Load the data and save the file. Open App overview by clicking on the Navigation dropdown  in the top-left corner: 
6. Add the Table object to the sheet.
7. Add MonthYear, Company Number, and Account Number as dimensions.
8. 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, 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.
9. Add the first expression to the table, and label it Opening:

Sum({<Month={"<=$(vMaxMonth)"}>} Opening) 

10. Add the second expression to the table, and label it Amount:

Sum({<Month={"<=$(vMaxMonth)"}>} Amount) 

11. Add the third expression to the table, and label it Closing:

Sum({<Month={"<=$(vMaxMonth)"}>} Closing) 

12. Under Sorting, promote Account Number to the top and set the sort order as numerically ascending.
13. Promote Company Number to the second position in sorting, and set the sort order as numerically ascending.

14. The final table report will look like this: