Skip to main content Skip to complementary content

Pivot table

The pivot table (Pivot) presents dimensions and measures as rows and columns in a table. In a pivot table you can analyze data by multiple measures and in multiple dimensions at the same time.

Pivot table, with expansions

Pivot object showing Sales, Quantity, and Margin % as measures for Customers. Customers is expanded into Product Group and Product type, showing measure results for these categories.

When to use it

Pivot tables are useful when you want to include several dimensions or measures in a single table, and then want to reorganize them to see different subtotals. The Qlik Visualization bundle pivot table offers several different layout and styling options than the pivot table visualization, including:

  • Headers for all dimensions

  • Header menu options for all dimensions

  • Custom null representation

  • Customizable column widths by pixel or percentage

  • Adjust column widths using mouse

  • Totals at top or bottom

Creating pivot tables

  1. In the assets panel, open Custom objects > Visualization bundle and drag Pivot to the sheet.
  2. Click Add and select a field or master dimension for the row dimension.

    Optionally, add more dimensions as rows. The pivot table drills down rows in the order they are added. You can click and drag the dimensions to reorder them.

  3. Click Add and select a field or master measure for the measure.

    Optionally, add more measures.

You can adjust appearance and other settings in the advanced properties panel.

Pivoting your data in the properties panel

In the properties panel, you can add measures and dimensions to the pivot table, as well as pivot rows or columns.

Data

In the data pane, you can add dimensions and measures. You can move items between rows and columns. You can also change item order inside rows or columns. When you use more than one measure, they are grouped and a Values item is created.

Properties panel, Data section.

Sorting

On the sorting pane in advanced properties, you can change the internal order of dimensions and measures.

Properties panel, sorting pane.

Information noteWhen sorting pivot tables, NULL values are always displayed first.

Sort by first measure

If you have more than one measure, a measure group is created. Sort by first measure will sort dimension values by the numeric value of the first measure. This sort order will affect all dimensions and take precedence before any other sort order defined under the dimensions.

For example, you have two measures: Quantity and Sales. In the properties pane, under Data> Measures, Quantity is listed first. If you Sort by first measure in descending order, your table is sorted starting with the dimension with the highest Quantity.

Pivot table in Edit mode. Sort by first measure has been toggled on.

Pivot object with sorted by first measure.

Sort by expression

When sorting by expression, the expression is only applied to the first dimension of a pivot table. Subsequent dimensions are sorted in the same order as the first dimension.

Formatting dimension and measure columns

You can format your dimension and measure columns with additional options. You can:

  • Create conditional columns

  • Set background color of cells using expressions

  • Set text color with expressions

  • Setting column width

Creating conditional columns

Columns can be conditional, so that they only appear when certain conditions are met.

  1. Under Data, select a dimension or measure and expand the properties.

  2. Under Show column if, click Expression to open the expression editor.

  3. Enter the expression to determine the conditions for the column to appear.

  4. Click Apply.

Setting background color with expressions

You can define the background color for a column using expressions. For example, you can have cells in the column change background color depending on the values in the column cell. The text color automatically changes to white when a dark background color is used.

  1. Under Data, select a dimension or measure and expand the properties.

  2. Under Background color expression, click Expression to open the expression editor.

  3. Enter the expression to determine the conditions for the column to appear.

  4. Click Apply.

Setting text color with expressions

You can define the text color for a column using expressions. For example, you can have cells in the column change text color depending on the values in the column cell.

  1. Under Data, select a dimension or measure and expand the properties.

  2. Under Text color expression, click Expression to open the expression editor.

  3. Enter the expression to determine the conditions for the column to appear.

  4. Click Apply.

Setting column width

By default, column width is automatically set. You can manually set column width by:

  • Content

  • Pixels

  • Percentage

You can also manually size the columns by dragging the header borders.

  1. Under Data, select a dimension or measure and expand the properties.

  2. Under Column width, select how you want to set column width:

    • Fit to content: Columns are sized by the width of the content in the column.

    • Pixels: Set the width of the column in pixels.

    • Percentage: Set the width of the column by percent.

    • Auto: Automatically set the column width with Qlik Sense.

Customizing styling

You have a number of styling options available under Appearance in the properties panel.

Click Styling under Appearance > Presentation to further customize the styling of the chart. The styling panel contains various sections under the General and Chart tabs.

You can reset your styles by clicking next to each section. Clicking Reset all resets styles in both General and Chart.

Customizing the text

You can set the text for the title, subtitle, and footnote under Appearance > General. To hide these elements, turn off Show titles.

The visibility of the different labels on the chart depends on chart-specific settings and label display options. These can be configured in the properties panel.

You can style the text that appears in the chart.

  1. In the properties panel, expand the Appearance section.

  2. Under AppearancePresentation, click Styling.

  3. On the General tab, set the font, emphasis style, font size, and color for the following text elements:

    • Title

    • Subtitle

    • Footnote

  4. On the Chart tab, set the font, emphasis style, font size, and color for the following text elements:

    • Header: Style the text of the headers for all dimensions.
    • Dimension values: Style the text dimension values.
    • Measure values: Style the text of the measure values.
    • Measure labels: Style the text of the measure headers.
    • Total values: Style the text of the totals.
    • Null values: Style the text of the null values.

Customizing the background

You can customize the background of the chart. The background can be set by color. You can also color individual dimensions or measures by expression.

  1. In the properties panel, expand the Appearance section.

  2. Under AppearancePresentation, click Styling.

  3. On the General tab of the styling panel, you can select a background color (single color or expression), and also set the background to an image from your media library.

    When using a background image, you can adjust image sizing and position.

  4. On the Chart tab, customize the background color for the following elements:

    • Header
    • Dimension values
    • Measure values
    • Measure labels
    • Total values
    • Null values

Customizing the grid

You can customize the pivot table grid.

  1. In the properties panel, expand the Appearance section.

  2. Under AppearancePresentation, click Styling.

  3. On the Chart tab, customize the grid with the following elements:

    • Row height: Set the height of rows in lines.

    • Border: Set the color of the cell borders.

    • Divider: Set the color of the divider between that divides both dimensions and measures as well as header and rows.

    • Background: Set the color of the empty area created when you have dimensions in Column.

Customizing null values

You can change how null values are represented in the pivot table.

  1. Click AppearancePresentation in the properties panel.

  2. Under Null value text, enter the text you want to replace null values with.

Expanding the pivot table fully

You can set the pivot table to always be fully expanded.

  1. Click AppearancePresentation in the properties panel.

  2. Select Fully expanded.

Customizing the border and shadow

You can customize the border and shadow of the chart.

  1. In the properties panel, expand the Appearance section.

  2. Under AppearancePresentation, click Styling.

  3. On the General tab of the styling panel, under Border, adjust the Outline size to increase or decrease the border lines around the chart.

  4. Select a color for the border.

  5. Adjust the Corner radius to control the roundness of the border.

  6. Under Shadow in the General tab, select a shadow size and color. Select None to remove the shadow.

Limitations

Pivot tables have the following limitations:

  • You can only download the pivot table as data.

  • You cannot use global grouping or limitations with the Visualization bundle pivot table as you can with the visualization pivot table.

  • You cannot pivot data in your Visualization bundle pivot table when your sheet is in analysis mode as you can with the visualization pivot table.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!