Creating pivot table using Excel table columns

You can create Excel pivot tables using levels. However, we recommend the following method, which is easier and faster.

Excel 2007 or later is required to complete the following procedures.

Create new Excel report

Do the following:

  1. Select Reports in the Qlik NPrinting main menu, and then click Create report.
  2. Enter a Title for the report. Pivot table report using Excel table.
  3. Select Excel from the Type drop-down list.
  4. Select an app from the App drop-down list.
  5. Click Create to create the report.
  6. Click Edit template to open the Template Editor.

Add object as table and change Keep Source Formats property

Do the following:

  1. Right-click the Tables node, and then select Add objects.
  2. Select the Connection to the QlikView document that contains the object you want.
  3. Add CH205 to the template as a table.

    Only QlikView straight tables can be used to create your template. If you want to use a QlikView pivot table, you must either convert it to a straight table or, if you want to preserve the original, clone it and convert the clone. Then add the converted table to your template.

  4. Right-click CH205 in the Tables node, and then select Properties.
  5. Clear the Keep Source Formats check box for CH205.

Construct table from columns

Construct the basic table by dragging columns separately into the template and dropping them into empty cells. You can use all or some of the columns.

Do the following:

  1. Expand the CH205 columns.
  2. Press the Shift key and select all the columns.
  3. Drag the columns and drop them into empty cells in the template.
  4. Open the Excel Ribbonby clicking on the Toolbar icon in the View group of the Template Editor toolbar.
  5. Adjust and format column headings as needed.
  6. Highlight the column heading, the tag, and one row below the cells to select them.
  7. Select the Insert tab on the Ribbon.
  8. Click Table in the Tables group of the Ribbon.
  9. Make sure the My table headers box is selected.
  10. Click OK.
  11. Drag and drop a deleterow tag into the column A of the first empty row below the table to remove it.

Convert to pivot table

Do the following:

  1. Highlight the column tag and heading cells to select them, if necessary.
  2. Select the Design tab under Table Tools on the Ribbon.
  3. Click Summarize with PivotTable in the Tools group.
  4. Click OK.

    The new pivot table will be created in a new worksheet. You can also add the pivot table in the same worksheet of the original data and, for example, hide the column with the data.

  5. Drag the Year field into the Columns box.
  6. Drag the Salesman and the Country fields into the Row Labels box.
  7. Drag the Sales field into the Values box, and then click on it.
  8. Click Count of Total sales to open the menu.
  9. Select Value Field Settings.
  10. Select Sum on the Summarize Values By tab in the Value Field Settings dialog window.
  11. Click OK.

Set data to be refreshed

Do the following:

  1. Click the Options dropdown menu under the PivotTable Tools - Analyze tab.
  2. Select the Data tab.
  3. Select the Refresh data when opening the file check box.
  4. Set Number of items to retain per field to None.
  5. Click OK at the bottom of the PivotTable Options dialog window.

If you do not perform these steps, you will see an empty pivot table in the produced report.

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.