Table properties

You can change the layout and formatting of tables in Qlik Sense.

You open the properties panel for a visualization by clicking Edit sheet in the toolbar and clicking the visualization that you want to edit.

If the properties panel is hidden, click  Show properties in the lower right-hand corner.

Data

Click Add column to add a dimension or a measure. Options that are dimmed are not available.

Columns

In the table, the dimensions and measures are presented together under the heading Columns.

Dimension settings

On the Data tab, click Add column and select Dimension to open a list of available Dimensions and Fields. Select the dimension or field that you want to use.

A dimension can either be selected from the list of already existing Dimensions, or created from the list of available Fields.

You can edit a dimension that is linked to a master item by updating the master item, or by unlinking the dimension from the master item and editing only this instance.

• <Dimension name>: Click the dimension name to open the dimension settings. If you want to delete the dimension, right-click the dimension and select in the dialog. Alternatively, click the dimension and click .
  • Field: If you have added a field from Fields in the assets panel, the field name is automatically displayed. Click to open the expression editor.
  • Label: Enter a label for the dimension. If you have added a field from Fields in the assets panel, the field name is automatically displayed.
  • Dimension: Only displayed for master items. To edit only this instance of the dimension, click to unlink the dimension from the master item.
  • Include null values: When selected, the measure values of all null dimensions are summarized and presented as one dimension item in a visualization. All null values are displayed as gaps or dashes (-).
  • Limitation: Limits the number of displayed values. When you set a limitation, the only dimensions displayed are those where the measure value meets the limitation criterion.

    • 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.

    • Calculated on measure <measure>: Shown when you make a limitation to the number of displayed dimension values. The dimensions whose measure value meet the criterion are displayed.

  • Show column if: You can show or hide a column depending on if a condition is true or false. The column is shown only if the expression in this field is evaluated as true. If this field is empty, the column is always shown.

    You can use this to require a field selection before calculating and showing large amounts of data.

  • Background color expression: Enter an expression for the background color. The text color automatically changes to white when a dark background color is used.

  • Text color expression: Enter an expression for the text color. If you use the same expression as in the background color, the text will not be visible.

    For more information, see Coloring a visualization.

  • Text alignment: When set to Custom, you can select how to display the content: Align left, Align center, or Align right. If Text alignment is set to Auto, the alignment is automatically aligned left or right, depending on the content.
  • Representation:
    •  When set to Text, all values are displayed as static text.
    • When set to Link, values that contain a URL are displayed as clickable links. Clickable links open a new browser tab. Users select a Link setting; either Add label or Add URL. When you set a link setting, only the corresponding option will be displayed.
      • Link setting: Add label
        • Link label: Enter a descriptive text label for the destination URL. Note that when the selected Link Setting is Add label, the dimension must contain the destination URL in either the dimension expression or the data table. The links will be displayed and ordered by the destination URL expression. Click to open the expression editor.
      • Link setting: Add URL
        • Link URL: Enter a destination URL expression. Note that when the selected Link Setting is Add URL, the dimension expression may contain only a label (that becomes the selectable text link). The links will be displayed and ordered by the link label expression. Click to open the expression editor.

    • When set to Image, values that contain an absolute URL of an image display as images. Users select an Image setting; either Add label or Add URL. When you set an image setting, only the corresponding option will be displayed.

      • Image setting: Add label
        • Image label: Enter a descriptive text label for the image that displays as Alternative Text when the image is hovered over. Note that when the selected Image Setting is Add label, the dimension must contain the absolute URL for the image in either the dimension expression or the data table. The links will be displayed and ordered by the URL (the absolute URL of the image) expression. Click to open the expression editor.
      • Image setting: Add URL
        • Image URL: Enter the URL (the absolute URL of the image) expression that will display when the image is hovered over. Note that when the selected Image Setting is Add URL, the user only needs to enter a label into the dimension expression. The links will be displayed and ordered by the link label expression. Click to open the expression editor.
      Tip note

      Image position follows Text Alignment settings.

      Tip note

      Image size and table layout can be modified by adjusting Row height settings with Content font size in Styling: Presentation.

      Information note

      The character ; is not supported as a query parameter separator in a URL.

  • Show others: When selected, the last value in the visualization (colored gray), summarizes all the remaining values. When some kind of limitation is set (Fixed number, Exact value, or Relative number), the value counts as 1 in that setting. If, for example, Exact value is used and set to 10, the tenth value is Others.
    • Others label: Enter a label for the summarized values (when Show others is selected). You can also use an expression as a label. Click to open the expression editor.
  • Master item: Create a master dimension based on the dimension expression by clicking Add new. If the dimension is already linked to a master item, click Edit to update the master item. All instances of the linked dimension will be updated.

You can drag a field from Measures to Dimensions to change how it is used. Moving a measure to Dimensions removes its aggregation. You cannot drag a master measure to Dimensions. You can only drag a measure to Dimensions if its expression is simple. For more information on expressions, see Using expressions in visualizations.

Measure settings

On the Data tab, click Add column and select Measure to open a list of available measures. Select the measure that you want to use. If you select a field, you are automatically presented with some common aggregation functions that you can choose between for the measure.

You can edit a measure that is linked to a master item by updating the master item, or by unlinking the measure from the master item and editing only this instance.

• <Measure name>: Click the measure to open the measure settings. If you want to delete the measure, click Delete.

  • Expression: Click to open the expression editor. The existing expression is displayed by default.

  • Modifier: Selects a modifier for the measure. Options are None, Accumulation, Moving average, Difference, and Relative numbers.
  • Accumulation: Only displayed when Accumulation has been selected as a modifier. It allows you to accumulate values of your measure over one or two dimensions.

  The following options are available:

  • Dimension: Selects the dimension on which the accumulation will take place. If the accumulation is made across multiple dimensions, this will be the first dimension.
  • Across all dimensions: Continues accumulation across all available dimensions.
  • Range: Select whether the accumulation will happen on the Full range, or a Custom range of the dimension.
  • Steps: Only displayed when Range is set to Custom. The number of steps in the dimension which form an accumulation range. Only zero and positive integers are accepted.
  • Show excluded values: Include excluded values in the accumulation.
  • Output expression: Shows the expression used by the modifier.
  • Moving average: Only displayed when Moving average has been selected as a modifier. It allows you to accumulate values of your measure over one or two dimensions.

  The following options are available:

  • Dimension: Selects the dimension on which the moving average will take place. If the moving average is made across multiple dimensions, this will be the first dimension.
  • Across all dimensions: Continues moving average across all available dimensions.
  • Range: Select whether the moving average will happen on the Full range, or a Custom range of the dimension.
  • Steps: Only displayed when Range is set to Custom. The number of steps in the dimension which form a moving average range. Only zero and positive integers are accepted.
  • Show excluded values: Include excluded values in the moving average.
  • Output expression: Shows the expression used by the modifier.
  • Difference: Only displayed when Difference has been selected as a modifier. It allows you to calculate the difference between consecutive values of a measure over one or two dimensions.

  The following options are available:

  • Dimension: Selects the dimension on which the difference will take place. If the difference is made across multiple dimensions, this will be the first dimension.
  • Across all dimensions: Continues difference across all available dimensions.
  • Show excluded values: Include excluded values in the difference.
  • Output expression: Shows the expression used by the modifier.
  • Relative numbers: Only displayed when Relative numbers has been selected as a modifier. It allows you to calculate percentages relative to a specific selection, relative to the total, or relative to a field value. The modifier can be calculated over one or two dimensions.

  The following options are available:

  • Selection scope: Sets whether the relative numbers are calculated based on the current selection or a specific field value. You can also disregard the current selection.
  • Dimensional scope: Sets whether the relative numbers are calculated based on a dimension. You can choose to have the modifier respect all available dimensions, respect a specific dimension if there are two, or to disregard the dimensions.
  • Output expression: Shows the expression used by the modifier.

• Label: Enter a label for the measure. Measures not saved in Master items are by default displayed with the expression as label.
• Measure: Only displayed for master items. To edit only this instance of the measure, click to unlink the measure from the master item.
• Number formatting: Different options for formatting the measure values. 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, that is, in the SET statements at the beginning of the script in the data load editor.

The following number formats are available:

• 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 drop-down list. Click the button to change to Custom formatting, and use the Format pattern box to change the 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 the same as set up in the operating system. Use the Format pattern box 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 drop-down list. Click the button to change to Custom formatting, and use the Format pattern box to change the format pattern.
• Duration: By default, the format pattern used for duration is the same as set up in the operating system. Duration can be formatted as days, or as a combination of days, hours, minutes, seconds and fractions of seconds. Use the Format pattern box to change the format pattern.
• Custom: By default, the format pattern used for custom is the same as set up in the operating system. Use the Format pattern boxes to change the format pattern.

• Measure expression: The format pattern is determined by the measure expression. Use this option to display custom number formatting for a measure in a visualization.

  Limitations:

  Only works with visualizations that accept measures.

  Cannot be used with a box plot.

  Does not affect the number formatting of the axis.

• Show column if: You can show or hide a column depending on if a condition is true or false.

  The column is shown only if the expression in this field is evaluated as true. If this field is empty, the column is always shown.

• Background color expression: Enter an expression for the background color. The text color automatically changes to white when a dark background color is used.

• Text color expression: Enter an expression for the text color. If you use the same expression as in the background color, the text will not be visible.

  For more information, see Coloring a visualization.

• Text alignment: When set to Custom, you can select how to display the content: Align left or Align right. If Text alignment is set to Auto, the alignment is automatically aligned left or right, depending on the content.
• Totals function: Select which aggregation function you want to use for the Totals row in the table. If Totals function is set to Auto, it will use the same function that is used in the measure expression. You can set the position of the Totals row under Presentation.

• Representation: You can select to show a value (Text) or an indicator (Indicator) or a Mini chart (Mini chart) or an Image (Image).

  The following properties are available if you select Indicator:

  • Add limit: Add indicator limits.
  • Gradient: Set the indicator color as a gradient according to the limit colors.
  • Show values: Show both the value and the indicator.
  • Apply color to value: Apply the indicator color to the value. This is only available when Show values is selected.
  • Indicator position: Select which side of the value to display the indicator. This is only available when Show values is selected.

  The following properties are available if you select Mini chart:

  • Select dimension: Select a dimension for the Mini chart.
  • Mode: Select a visualization mode for the Mini chart. Bars, Dots, Sparkline, and Positive/negative are available.
  • Colors: Set optional colors for the mini chart.
  • Show dots: Adds dots to each data point on the sparkline mini chart.
  • Include null values: When selected, the measure values of all null dimensions are summarized and presented as one dimension item in a visualization. All null values are displayed as gaps or dashes (-).
  • Show others: When selected, the last value in the visualization (colored gray), summarizes all the remaining values. When some kind of limitation is set (Fixed number, Exact value, or Relative number), the value counts as 1 in that setting. If, for example, Exact value is used and set to 10, the tenth value is Others.
  • Y-axis: Select what value to use for the Y-axis of the mini chart.

  The following properties are available if you select Image:

  • Image Setting: Select either Add label or Add URL 
    • Image setting: Add label
      • Image label: Enter a descriptive text label for the image that displays as Alternative Text when the image is hovered over. Note that when the selected Image Setting is Add label, the measure expression must contain the absolute URL for the image in either the measure expression or the data table. The links will be displayed and ordered by the URL (the absolute URL of the image) expression. Click to open the expression editor.
    • Image setting: Add URL
      • Image URL: Enter the URL (the absolute URL of the image) that will display when the image is hovered over. Note that when the selected Image Setting is Add URL, the images will be displayed and ordered by the measure label expression. Click to open the expression editor.
  Tip note

  Image position follows Text Alignment settings.

  Tip note

  Image size and table layout can be modified by adjusting Row height settings with Content font size in Styling: Presentation.

• Master item: Create a master measure based on the measure expression by clicking Add new. If the measure is already linked to a master item, you can update the master item by clicking Edit. All instances of the linked dimension will be updated.

You can drag a field from Dimensions to Measures to change how it is used. Moving a dimension to Measures assigns it an aggregation. You cannot drag a master dimension to Measures.

Scripts

On the Data tab, under Scripts, click Add to create a chart script. You can add more than one chart script.

You can enter the chart script directly in the text box, or click to create a chart script in the expression editor.

Using chart level scripting in visualizations

Sorting

Drag the dimensions and measures to set the sorting priority order. The numbers show the order.

Dimensions and measures can also be sorted internally:

Click the dimension or measure name to open the settings and click the sorting button to switch to Custom sorting. The following list shows the internal sorting priority order and sorting options. The sorting is either Ascending or Descending.

• Sort by expression: Enter an expression to sort by. Only available for dimensions.
• Sort numerically
• Sort alphabetically

Additionally, you can sort by load order by switching to Custom and leaving all sorting options unselected.

If you have set a custom order for a field, that custom order will override any selected internal sort order in Sorting. For more information, see Customizing the order of dimension values.

Add-ons

• Data handling: 

  • Include zero values: When unselected, measures that have the value ‘0’ are not included in the presentation. If there is more than one measure value, all the measure values must have the value ‘0’ to be excluded from the presentation.

  • Calculation condition: Specify an expression in this text field to set a condition that needs to be fulfilled (true) for the object to be displayed. The value may be entered as a calculated formula. For example: count(distinct Team)<3. If the condition is not fulfilled, the message or expression entered in Displayed message is displayed.

    A calculation condition is useful when a chart or table is slow to respond due to a large amount of data. You can use the calculation condition to hide an object until the user has filtered the data to a more manageable level by applying selections. Use the Displayed message property to guide the user to filter the data.

    Using Calculation Conditions

Appearance

General

• Show titles: On by default in all visualizations except filter panes, KPIs, and text & image visualizations.

  Enter Title, Subtitle, and Footnote. By default, the string is interpreted as a text string. However, you can also use the text field for an expression, or a combination of text and expression. An equals sign (=), at the beginning of a string shows that it contains an expression.

  Click if you want to create an expression by using the expression editor.

  Information noteTitles are displayed on a single line. If you inject line breaks they will be ignored.

  Example:  

  Assume that the following string is used, including quotation marks: 'Sales: ' & Sum(Sales).

  By default, the string is interpreted as a text string and is displayed as presented in the example. But if you begin the string with an equals sign: (='Sales: ' & Sum(Sales)), the string is interpreted as an expression instead. The output is then Sales: <value of expression>, where <value of expression> is the calculated value.

• Disable hover menu: Select to disable the hover menu in the visualization.

• Show details: Set to Show if you want to allow users to be able to choose to view details, such as descriptions, measures, and dimensions.

Alternate states

State: Set the state to apply to the visualization. You can select:

• Any alternate state defined in Master items.
• <inherited>, in which case the state defined for the sheet is used.
• <default state>, which represents the state where no alternate state is applied.

For more information about alternate states, see Using alternate states for comparative analysis.

Presentation

• Styling: You can change the styling of the table by clicking on Styling. You can reset your styles by clicking .

  • General: General styling controls font styles for titles, subtitles, and footnotes in the chart. For each option, you can select the font, emphasis style, font size, and color.

  • Chart: Customize the styling of the table, overriding the app theme. You can add custom header, content font sizes, row height (in lines), and colors. You can set rows to be highlighted when hovered over and set colors for the row and font. You can also set the size of the scrollbar.

• Totals: 
  • Auto: The totals (the result of the expression), are automatically included at the top of the table.
  • Custom: Select whether to display the totals and where to display them, at the top or bottom.
• Totals label: Set the label for the totals row. You can also use an expression as a label.

• Horizontal scrolling: Use the drop down to choose between:

  • Scroll: Enable horizontal scrolling. Use the toggle to freeze the first column when scrolling.
  • Column picker: Enable column picker feature. App consumers can change column order in the table.
• Multiline text wrapping: Enable or disable multiline text wrapping in headers and cells separately.

• Expression editor
• Properties panel