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.

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

Do the following:

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.

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.

Do the following:

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

2. Under Show column if, click 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.

Do the following:

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

2. Under Background color expression, click 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.

Do the following:

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

2. Under Text color expression, click 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.

Do the following:

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.

Do the following:

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

2. Under Appearance > Presentation, 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.

Do the following:

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

2. Under Appearance > Presentation, 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.

Do the following:

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

2. Under Appearance > Presentation, 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.

Do the following:

1. Click Appearance > Presentation 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.

Do the following:

1. Click Appearance > Presentation in the properties panel.

2. Select Fully expanded.

Customizing the border and shadow

You can customize the border and shadow of the chart.

Do the following:

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

2. Under Appearance > Presentation, 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.