Totals at the Feet of Columns in Tables

    When your report includes a table or one or more columns from tables, it is often desirable to display a sum for the columns where appropriate. The first example is from a table that has been embedded in the template column by column. The second example contains a table with calculated columns, which requires the application of a level.
       
         

    Open Select Object Window

                   
                Open-Select-Object-Window.png     
         
    1. Right click on the Tables node
    2. Select Add object
       
       
       
         

    Add Table Object to Template

                   
                Add-Table-Object-to-Template.png    
         
    1. Select the Connection to the QlikView document that contains the object that you want
    2. Select the Top 25 Products - CH184
    3. Click on the OK button
       
       
       
         

    Embed Columns in Template

                   
         Embed-Columns-in-Template.png            
         
    1. Expand the CH184 - Top 25 Products node by clicking on the '+' to its left
    2. Drag the field node tokens into the template one by one and drop them into empty cells. They expand to produce a heading and a field tag.
    3. Apply Excel formatting to the headings and columns (see Can I apply Excel formatting to my Excel reports?)
       
       
       
         

    Set up Total Row

                   
                Set-up-Total-Row.png      
         
    1. Select a cell below the field tag so that there is at least one intervening empty cell
    2. Enter the Excel sum function formula either in the cell editing field or directly in the cell by double clicking on it

    The sum range should include the field tag cell address, D4 in this example, and the address of the empty cell below it, D5 in this case, separated by a colon.

       
       
       
         

    Format Total Row

                   
                Format-Total-Row.png 
         
    1. Apply Excel formatting tools to the elements of the Total row. The Product Name and Total Sales elements in the Total row have the General Number format, while the Gross Profit element has the Currency format with no decimal places. This is so that you can see the difference.
    2. Click on the Save As icon in the Actions group
       
       
       
         

    Request Preview

                   
                Request-Preview.png
         

    Click on the Preview button

       
       
       
         

    Preview Results

                   
                Preview-Results.png
         

    Note the form of the number without specific numerical formatting that you can see in the Total Sales column.

       
       
       
         

    Use Results from "How to Make Calculated Columns in Excel Reports"

                   
                Use-Results-from--How-to-Make-Calculated-Columns-i.png
         

    This part starts from the results of How to Make Calculated Columns in Excel Reports which contains a a table in a level. If you need help with levels, see How to Create One or More Levels in Excel Reports.

    1. Using the table created in the above cited tutorial, select an empty cell below the row containing the level closure tag, </CH184_Level> in this example
    2. Enter the Excel sum function formula either in the cell editor field or directly in the cell by double clicking on it
    3. Format the totals as you prefer
    4. Drag the tag <deleterow> from the Extras tab to the cell A12. This will remove the row 12 from the final report

    The range of the sum function needs to include the address of the cell in the row containing the level opening tag, the cell containing the field tag, and the address of at least the empty cell immediately below it. In this example, the function could read =SUM(E9:E11).

       
       
       
         

    Preview Results

                   
                Preview-Results.png
         

    Remember to Save and Close the template editor, then click on Preview.