Adding calculated columns

When you create an Excel report, you may need to create a column that does not exist in the original QlikView document or Qlik Sense app, and then have the column calculate the values of other columns using formulas. You can implement calculated columns in two different ways.

The first way uses levels. You insert field tags in a level and then add Excel formulas where necessary. This is a simple and fast-to-implement approach, but using levels can impact report creation performance.

The second way uses an Excel table. You create the Excel table using Excel table tools to add calculated columns and to manage the table. This approach results in better report creation performance, but it requires more advanced Excel skills. We recommend using this approach. If you want to minimize report creation time, we recommend avoiding calculations in Excel, and instead moving the calculations into the QlikView reload script.

This page shows how to create calculated columns using both approaches. To demonstrate, a third column will be calculated by subtracting the values in one column from the values in another column. Then the derived values of the third column will be divided by the corresponding values from one of the original two columns.

This simple example illustrates how to create calculated columns. You can insert more complex Excel formulas in many different columns.

Add table as level

This is the first way to add calculated columns, using levels.

Do the following:

  1. Select Reports in the Qlik NPrinting main menu, and then click Create report.
  2. Enter a Title for the report, for example: Calculated columns.
  3. Select Excel from the Type drop-down list.
  4. Select an app from the App drop-down list. For example, select Sales Demo.
  5. Click Create to create the report.
  6. Click Edit template to open the Template Editor.
  7. Right-click the Levels node, and then select Add levels.
  8. Select one or more charts that have the fields that you want to combine to create an additional field. For example, add CH184.
  9. Click OK.
  10. Drag the chart node into the template and drop it onto the first cell of three vertically aligned empty cells.
  11. Expand the table node(s).
  12. Select all of the columns of CH184 and then drag them into the template, dropping the columns into empty cells of the row that is between the rows containing the level tags.

Enter formula to be calculated

Do the following:

  1. Select the first cell on the right of the field tags.
  2. Enter the formula Total Sales - Gross Profit, as you normally would in Excel.
  3. Click on the cell, type = then click on the Total Sales cell.
  4. Type the -, click on the Gross Profit cell and confirm by pressing enter.

    You can just insert one formula between the level opening and closing, and Qlik NPrinting will copy it into all the rows of the final table. You can ignore the Excel error #VALUE! in the template; you see it because the Excel formula is referencing cells that contain field tags.

  5. Format the formula cell as you prefer using Excel formatting features.
  6. Enter headings for each of the columns in a row located one or two rows above the row containing the CH184 tag, and then format as required.

Add Excel table

This is the second way to create calculated columns. You will add the source QlikView object to the Tables node, and then create an Excel table.

Do the following:

  1. Right-click the Tables node, and then select Add objects.
  2. Select one or more charts that have the fields that you want to combine to create an additional field. For example, add CH184.
  3. Click OK.
  4. Expand the table node(s).
  5. Select all columns, and then drag and drop them into the template.
  6. Select the Excel cells with the heading, the field tags, and one more row below.
  7. Open the Insert tab in the Excel ribbon bar, and then click Table.
  8. Be sure that the My table has headers flag is activated, and then click OK.
  9. Drag a deleterow tag into the one cell of the empty row below the table.

    This will remove the empty row from the final report.

Add the calculated column

Do the following:

  1. Select the first cell on the right of the field tags.
  2. Insert the formula =[@[Total Sales]]-[@[Gross Profit]].

    Excel will automatically add this new column to the table.

  3. Add Cost as a column heading.
  4. To customize the table formats, clear the Keep Source Format option for CH184, and then use the Excel table design tool to apply the new format.

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.