Creating calculated columns and rows with Excel formulas

Excel formulas let you add calculated columns and row totals that do not exist in your original QlikView document or Qlik Sense app.

What you will do

You will:

1. Add row totals using the SUM function.
2. Add calculated columns. You can do this in two different ways:

1. Excel tables: You create a table using Excel table tools. This method results in better report creation performance, but requires more advanced Excel skills. We recommend using this approach.

2. Levels: You insert field tags in a level, and then add Excel formulas. This is easy to implement, but using levels can impact report generation performance.

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 tutorial uses QlikView data that can be found in Sample files. You can also use your own QlikView or Qlik Sense data.

About calculations

If you want to minimize report creation time, you can add desired calculations to the QlikView or Qlik Sense reload script.

This example uses simple Excel formulas, but you can insert more complex formulas if needed. You can also insert Qlik formulas. See: Qlik Sense and QlikView formulas.

Adding an Excel table with calculated columns and totals

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

Adding the table

Do the following:

1. Create a new Excelreport, or open an existing one.

2. Right-click the Tables node, and select Add objects.

3. Select a chart from the list. For example, add Top 10 Products. Click OK.

   

4. Click + on the left to expand the CH184_Table - Top 10 Products table node.
5. One at a time, select each column under CH184_Table - Top 10 Products, and clear Keep Source Formats.

6. Hold CTRL and select the table fields you want to add to the sheet.

   For example: Product Name, Total Sales and Gross Profit. Drag them onto an empty row of cells.

   

7. Select the cells that contain the heading, the field tags, and one blank row below.
8. In the Excel ribbon, click the Insert tab, and then click Table.

9. Make sure that the My table has headers flag is enabled, and then click OK.

   

10. On the left pane, click Extras. Drag a deleterow tag onto a cell in the empty row below the table.

    

    This will remove the empty row from the final report.

Adding the calculated column

Do the following:

1. Select the first empty cell to 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. Type a column heading. For example: Costs.

4. Then use the Excel table design tool to apply formatting.

Adding totals

Do the following:

1. Select an empty cell below the table. For example, D8.

2. Enter a SUM formula in the cell. 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.
   • The address of at least one empty cell immediately below it.

   In this example, the function would read: =SUM(D4:7).

3. Click and drag this formula to E8 and F8.

4. Apply Excel formatting tools to the elements of the row.

   For example: Total Sales and Gross Profit totals could have the Currency format.

   

Previewing the report

Do the following:

1. Click Preview.

   Excel launches and displays your report.

2. You will see a table, with the one column containing your calculated results. The bottom row will contain column totals.

   

3. Click Save and Close to save the template, and close the Template Editor.

Using levels to build a table with calculated columns and totals

This is the second way to add calculated columns, using levels. To learn about levels, see: Excel reports with levels.

Adding the level

Do the following:

1. Create a new Excel report, or open an existing one.
2. Right-click the Levels node, and select Add levels.

3. Select a chart from the list. For example, add Top 10 Products. Click OK.

   

4. Click the CH184_Level - Top 10 Products node, and drag it onto three empty vertically-aligned cells.

   

5. Click the + on the left to expand the CH184_Level - Top 10 Products level node.
6. One at a time, select each column under CH184_Level - Top 10 Products, and clear Keep Source Formats.

7. Hold CTRL and select the level fields you want to add to the sheet.

   For example: Product Name, Total Sales, and Gross Profit. Drag them onto the empty row of cells between the level tags.

   

8. Format these fields as you prefer. For example, you could format Total Sales as currency.

Adding formulas and headings

1. Select the first empty cell to the right of the field tags. In this example, F4.
2. Click on the cell, type = then click on the Total Sales cell.

3. Type -, click on the Gross Profit cell and press Enter. This will calculate costs.

   When you insert a formula between level tags, Qlik NPrinting will copy it into every row of the final table.

   You can ignore the error #VALUE! in the template. This occurs because the Excel formula references cells that contain field tags.

4. In cell G4, type =F5/D5. This will divide costs by sales.

5. Format the formula cells as you prefer using Excel formatting.

   For example, costs as currency, and costs/sales as a percentage.

6. Type in headings for each column in a row located one or two rows above the row containing the Top 10 Products opening level tag.

   Format these headings as desired.

   

7. Select a cell below the </Top 10 Products_Level> level closing tag so that there is at least one intervening empty cell.

8. In cell D8, enter an Excel SUM formula to create a row of column totals. The sum range should include:

   1. The field tag cell address.
   2. The address of the empty cell below it.

   In this example, the function would read: =SUM(D4:D6).

9. Click and drag this formula to E8 and F8.

10. Apply Excel formatting tools to the elements of the row.

    For example: Total Sales and Gross Profit totals could be formatted as currency.

    

Previewing the report

Do the following:

1. Click Preview.

   Excel launches and displays your report.

2. You will see a table, with the two new columns containing your calculated results. The bottom row will contain column totals.

   

3. Click Save and Close to save the template, and close the Template Editor.