Totals of columns in tables

When your report includes a table, or one or more columns from tables, you may often want to show sums for the columns. The first example below 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

Do the following:

  1. In the Template Editor, right-click the Tables node, and then select Add object.
  2. Select the Connection to the QlikView document that contains the object you want.
  3. Select CH184 from the objects list.
  4. Click OK.
  5. Expand the CH184 node.
  6. 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.

  7. Apply Excel formatting to the headings and columns.
  8. Select a cell below the field tag so that there is at least one intervening empty cell.
  9. Enter the Excelsum 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, separated by a colon.

  10. 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.

  11. Click Save in the Actions group.
  12. Click Preview to preview the result.

Make calculated columns in Excel reports

This procedure requires that you first complete Adding calculated columns which produces a table in a level. If you need help with levels, see Creating one or more levels in Excel reports.

Do the following:

  1. Using the table created above, select an empty cell below the row containing the level closing tag, </CH184_Level> in this example.
  2. Enter the Excel sum function formula in either the cell editor field or directly in the cell by double-clicking it.
  3. Format the totals.
  4. Drag deleterow node token from the Extras tab to the A12 cell.

    This will remove the row 12 from the final report.

  5. Note:

    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 and save

Do the following:

  1. Click Preview.
  2. Check the report, close it, and then edit the template again if necessary.
  3. Click Save and Close to save the template and close the Template Editor.

Did this information help you?

Thanks for letting us know. Is there anything you'd like to tell us about this topic?

Can you tell us why it did not help you and how we can improve it?