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
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:
- In the advanced edit mode assets panel, open Custom objects > Qlik Visualization bundle and drag Pivot to the sheet.
-
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.
-
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.
Sorting
On the sorting pane in advanced properties, you can change the internal order of dimensions and measures.
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.
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:
-
Limit displayed dimensions
-
Format dimension representation
-
Format measure numbers
-
Create conditional columns
-
Set background color of cells using expressions
-
Set text color with expressions
-
Setting column width
Limiting displayed dimensions
Limit the number of displayed values for a dimension. When you set a limitation, the only dimensions displayed are those where the measure value meets the limitation criterion.
Do the following:
-
Under Data, select a dimension and expand the properties.
-
Under Limitation, select one of the following limitations:
-
No limitation: The default value.
-
Fixed number: Select to display the top or bottom values. Set the number of values. You can also use an expression to set the number. Click
to open the expression editor. -
Exact value: Use the operators and set the exact limit value. You can also use an expression to set the number. Click
to open the expression editor. -
Relative value: Use the operators and set the relative limit value in percent. You can also use an expression to set the number. Click
to open the expression editor.
-
Formatting dimension representation
You can format dimension columns to show values, links, or images using Representation in the properties panel. You can select the following representations:
- Text: Displays the dimension values as text. This is the default value.
-
Link: Displays values as clickable links. Either the dimension value is used or the URL or you can add other URLs to the displayed dimension values.
-
Image: Displays values that contain an absolute URL to an image display as images.
Formatting links
The character ; is not supported as a query parameter separator in a URL.
Do the following:
-
Under Data, select a dimension and expand the properties.
-
Under Representation, select Link.
-
Under Link setting, select one of the following:
-
Add label: The dimension contains the destination URL in either the dimension expression or the data table. The links are displayed and ordered by destination URL expression.
Under Link label, enter a descriptive text label for the destination URL. Click
to open the expression editor. -
Add URL: The dimension values become selectable text links that are labels for the URLs.
Under Link URL, enter the destination URL expression. Click
to open the expression editor.
-
Formatting measure numbers
You can format measure numbers under Number formatting in the properties panel.
If you want to change the number format at app level, and not just for a single measure, it is better to do that in the regional settings in the SET statements at the beginning of the script in the data load editor.
Do the following:
-
Under Data, select a measure and expand the properties.
-
Under Number formatting, select the format:
-
Auto: Qlik Sense automatically sets a number formatting based on the source data.
To represent numeric abbreviations, the international SI units are used, such as k (thousand), M (million), and G (billion).
-
Number: By default, the formatting is set to Simple, and you can select the formatting from the options in the list. Change to Custom to and enter the format pattern under Format pattern.
Examples:
- # ##0 describes the number as an integer with a thousands separator. In this example " " is used as a thousands separator.
- ###0 describes the number as an integer without a thousands separator.
- 0000 describes the number as an integer with at least four digits. For example, the number 123 will be shown as 0123.
- 0.000 describes the number with three decimals. In this example "." is used as a decimal separator.
If you add the percent sign (%) to the format pattern, the measure values are automatically multiplied by 100.
- Money: By default, the format pattern used for money is set by the preferred region settings in your profile. Use Format pattern to change the format pattern.
- Date: By default, the formatting is set to Simple, and you can select the formatting from the options in the list. Change to Custom to and enter the format pattern under Format pattern.
- Duration: By default, the format pattern used for duration is set by the preferred region settings in your profile. Duration can be formatted as days, or as a combination of days, hours, minutes, seconds and fractions of seconds. Use Format pattern to change the format pattern.
- Custom: By default, the format pattern used for custom is set by the preferred region settings in your profile. Use Format pattern to change the format pattern.
-
Creating conditional columns
Columns can be conditional, so that they only appear when certain conditions are met.
Do the following:
-
Under Data, select a dimension or measure and expand the properties.
-
Under Show column if, click
to open the expression editor. -
Enter the expression to determine the conditions for the column to appear.
-
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:
-
Under Data, select a dimension or measure and expand the properties.
-
Under Background color expression, click
to open the expression editor. -
Enter the expression to determine the conditions for the column to appear.
-
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:
-
Under Data, select a dimension or measure and expand the properties.
-
Under Text color expression, click
to open the expression editor. -
Enter the expression to determine the conditions for the column to appear.
-
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:
-
Under Data, select a dimension or measure and expand the properties.
-
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
With Advanced options turned on, 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:
-
In the properties panel, expand the Appearance section.
-
Under Appearance > Presentation, click
Styling. -
On the General tab, set the font, emphasis style, font size, and color for the following text elements:
-
Title
-
Subtitle
-
Footnote
-
-
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. You can also color individual dimensions or measures by expression.
Do the following:
-
In the properties panel, expand the Appearance section.
-
Under Appearance > Presentation, click
Styling. -
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 color, use the slider to adjust the opacity of the background.
When using a background image, you can adjust image sizing and position.
-
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:
-
In the properties panel, expand the Appearance section.
-
Under Appearance > Presentation, click
Styling. -
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:
-
Click Appearance > Presentation in the properties panel.
-
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:
-
Click Appearance > Presentation in the properties panel.
-
Select Fully expanded.
Customizing the border and shadow
You can customize the border and shadow of the chart.
Do the following:
-
Click Advanced options.
-
In the properties panel, expand the Appearance section.
-
Under Appearance > Presentation, click
Styling. -
On the General tab of the styling panel, under Border, adjust the Outline size to increase or decrease the border lines around the chart.
-
Select a color for the border.
-
Adjust the Corner radius to control the roundness of the border.
-
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 pivot data in your Visualization bundle pivot table when your sheet is in analysis mode as you can with the visualization pivot table.