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

    1. Right click on the Tables node
    2. Select Add object

    Add Table Object to Template

    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

    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

    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

    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


    Click on the Preview button


    Preview Results


    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"


    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


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