Qlik Community

Qlik NPrinting Documents

Documents related to Qlik NPrinting.

How to Create a Pivot Table Report with QlikView Data

Not applicable

How to Create a Pivot Table Report with QlikView Data

Excel Pivot tables can be created using levels (see: How to Create an Excel PivotTable Report), but the following method is simpler and quicker and we recommend it. Excel 2007 or a later version is necessary for this procedure. Here's how.

  
    

Create New Excel Report

              
            Create-New-Excel-Report.png              
    
  1. Select Reports in the lower left pane
  2. Select Excel Reports in the upper left pane
  3. Click on Excel Report in the New group of the tool bar
  
  
  
    

Create New Template

              
            Create-New-Template.png              
    

Click on New in the Template group of the tool bar

  
  
  
    

Save New Template as .xlsx

              
            Save-New-Template-as-xlsx.png              
    

The template must be saved in .xlsx or .xlsm format to be able store the information necessary for a pivot table.

Click on the Save As icon

  
  
  
    

Select Save as Type:

              
            Select-Save-as-Type-.png              
    

Expand the Save as type: drop-down menu and select Excel Workbook (*.xlsx)

  
  
  
    

Conclude Saving Process

              
           Conclude-Saving-Process.png
    
  1. Note that the file extension has been changed to .xlsx
  2. Click on the Save button
  
  
  
    

Add Object as Table and Change "Keep Source Formats" Property

              
            Add-Object-as-Table-and-Change--Keep-Source-Format.png              
    
  1. Add CH267 to the template as a Table (See: the "Add QlikView Objects as Tables: Selection" step in How to Create Excel Reports). Only QlikView Straight Tables can be used to create your template. If you want to use a QlikView PivotTable you must either convert it to a straight table or, to preserve the original, clone it and convert the clone. Then add the converted table to your template.
  2. Unflag the Keep Source Formats box in the Properties dialog window for CH267 (See: Can I apply Excel formatting to my Excel reports?)
  
  
  
    

Construct Table from Columns

              
            Construct-Table-from-Columns.png              
    
  1. Construct the basic table by dragging columns separately into the template and dropping them onto empty cells. You can use only some of columns, if you wish.
  2. Open the Toolbar by clicking on the Toolbar icon in the View group of the NPrinting toolbar
  3. Adjust  and format column headings as needed
  
  
  
    

Create Table

              
            Create-Table.png              
    
  1. Highlight the column tag and heading cells to select them
  2. Select the Insert tab on the Excel toolbar
  3. Click on Table in the Tables group of the Excel toolbar
  
  
  
    

Complete Table Conversion

              
            Complete-Table-Conversion.png              
    
  1. Make sure the My table has headers box is checked
  2. Click on the OK button
  
  
  
    

Convert to Pivot Table

              
            Convert-to-Pivot-Table.png              
    
  1. Re-highlight the column tag and heading cells to select them if necessary
  2. Select the Design tab under Table Tools on the Excel toolbar
  3. Click on Summarize with PivotTable in the Tools group of the Excel toolbar
  
  
  
    

Conclude PivotTable Creation

              
            Conclude-PivotTable-Creation.png              
    
  1. Click on the OK button
  
  
  
    

Organize Dimensions

              
            Organize-Dimensions.png              
    
  1. Drag the Year Qtr field into the Column Labels box
  2. Drag the Total Sales field into the Values box
  3. Drag the Category Name and Country fields into the Row Labels box
  
  
  
    

Open Value Field Settings Dialog Window

              
            Open-Value-Field-Settings-Dialog-Window.png              
    
  1. Click on the Count of Total Sales button to open the menu
  2. Select Value Field Settings...
  
  
  
    

Adjust Value Field Settings

              
            Adjust-Value-Field-Settings.png              
    
  1. Select Sum on the Summarize Values By tab in the Value Field Settings dialog window
  2. Click on the OK button
  
  
  
    

Set Data to Be Refreshed

              
            Set-Data-to-Be-Refreshed.png              
    
  1. Select the Options tab under PivotTable Tools
  2. Click on the Options icon in the PivotTable group
  3. Select the Data tab
  4. Check the Refresh data when opening the file checkbox
  5. Set Number of items to retain per field: to None
  6. Click on the OK button at the bottom of the PivotTable Options dialog window
  
  
  
    

Preview

              
            Preview.png              
    

Click on the Preview icon

  
  
  
    

View PivotTable Report

              
            View-PivotTable-Report.png              
    

Give a sigh of satifaction.

  
  
  
    

Conclude Template Creation

              
            Conclude-Template-Creation.png              
    

Click on Save and Close

  
  
  
    

Save Your Work in NPrinting File

              
            Save-Your-Work-in-NPrinting-File.png              
    

Click on Save and Close

  
Note:
If you need to add another field to the pivot table:
  • Add the field to the straight table sheet
  • Go to Pivot Table Tools > Analyze and click the Refresh button
  • The new field will be available to the Pivot
Comments
woutermak
Contributor III

Very usefull. Works for me

agrimroquette
Valued Contributor

really great work and effort

Not applicable

I get this error, looks like NP designer 17.3.0 does not let me use excel functions transformation.

not using levels, neither using columns from table object

is there any tricks to add extra blank lines or anything to make it work?

thanks!

error designer - copia.png

sitrakiniaina_j
Contributor II

Hello !

Thank you for this Document.

I have a question, level works with crosstable created or not?

I am trying to create a pivot table with field TypeLicense and I want to create a level with this field in question but failure....

If you have a workaround I am interested.


Thanks in advance

Version history
Revision #:
1 of 1
Last update:
‎04-23-2015 08:31 AM
Updated by: