How do I troubleshoot issues in my QuickSight table visuals?

Resolution

Resolve table sorting issues

If your table visual doesn't correctly sort or you can't sort the metrics in the table, then take the following actions.

Check the column data type

QuickSight sorts columns based on their data type. If a text column contains numeric values, then QuickSight sorts the values alphabetically.

To resolve this issue, change the data type of the column to a numeric type in the dataset settings. Or, use the parseInt() or decimalToInt() function to create a calculated field.

Check visual sort order

To check the visual sort order, complete the following steps:

1. Open the Amazon Quick console.
2. In the navigation pane, choose Analyses.
3. Select the analysis that contains the table visual.
4. In the table visual, select the column header, and then choose Sort order and then choose Ascending or Descending.

If you can't sort a column, then add the column to the Group by or Value field wells. You can't independently sort columns that aren't in field wells.

To add the column to the field wells, complete the following steps:

1. Open the Amazon Quick console.
2. In the navigation pane, choose Analyses.
3. Select the analysis that contains the table visual.
4. Choose the field well at the top of the visual.
5. Drag the column from the Fields list into the Group by or Value section.

Use a calculated field to multi-level sort

If a table has multiple value columns, then QuickSight sorts one metric at a time. To multi-level sort, use a calculated field to combine sort criteria, or use the dataset-level sort before you add data to the visual.

Manually set the bar chart axis sort order

If a bar chart axis automatically adjusts the sort order, then manually set the sort field.

Complete the following steps:

1. Open the Amazon Quick console.
2. In the navigation pane, choose Analyses.
3. Select the analysis with the table visual.
4. Select the visual.
5. Choose Sort.
6. To override the automatic sort behavior, set the sort field to the dimension.

Resolve subtotal and aggregation issues

If subtotal columns don't correctly sum in your table visual, then take the following actions.

Check the aggregation type

Complete the following steps:

1. Open the Amazon Quick console.
2. In the navigation pane, choose Analyses.
3. Select the analysis.
4. Choose the visual.
5. Choose the column header.
6. Choose Aggregate, and then choose the aggregation type.

If the column uses a custom calculated field, then make sure that the formula produces the expected result at the subtotal level.

Check for filters that affect subtotals

If you apply filters at the visual level, then QuickSight might incorrectly calculate your subtotals. Check that the filters include data that should be in the subtotal calculation. If you must display filtered data when you maintain accurate totals, then use table calculations instead of filters.

Review totals and subtotals settings

Turn on totals. Flat table visuals support only totals. If you use subtotals for grouped data, then use a pivot table visual.

Complete the following steps:

1. Open the Amazon Quick console.
2. In the navigation pane, choose Analyses.
3. Select the analysis.
4. Select the table visual.
5. Choose Format visual.
6. Under Totals, turn on show totals, and then configure total placement.

Resolve conditional formatting issues

If conditional formatting doesn't work as expected for your table visual, then take the following actions.

Check the conditional formatting rules

Complete the following steps:

1. Open the Amazon Quick console.
2. In the navigation pane, choose Analyses.
3. Select the analysis.
4. Select the table visual.
5. Choose Menu options, and then choose Conditional formatting.
6. Confirm that you applied the rules to the column and that the conditions match your expected criteria.
7. Make sure that you correctly configured the destination formatting.

Check field data types

Conditional formatting rules depend on data types. If your column contains numeric data stored as strings, then change the data type in the dataset. Or, create a calculated field that converts the value to a number.

Add heatmap-style formatting

To add a heatmap and identify trends, see Creating a heat map

Add data bars to table visuals

Complete the following steps:

1. Open the Amazon Quick console.
2. In the navigation pane, choose Analyses.
3. Select the analysis.
4. Select the table visual.
5. Choose Format visual.
6. Under Visuals, choose Apply data bars.
7. In the Value field, select the destination numeric field, and then configure the positive and negative bar colors.
   Note: If the data bars option isn't available, then check that the column is a numeric measure in the Value field well.

Resolve column display and responsiveness issues

If your table columns aren't responsive or don't display as expected, then take the following actions.

Switch to tiled layout mode

In classic layout mode, table columns have fixed widths and don't resize when you resize the browser window. To activate responsive column behavior, switch to the Tiled or Free-form layout mode.

Manually adjust column widths

To manually resize column widths, see Row and column size in tables and pivot tables in Quick.

Use the fit-to-width option

To force columns to fit within the visible area of the visual and automatically distribute available space among columns, complete the following steps:

1. Open the Amazon Quick console.
2. In the navigation pane, choose Analyses.
3. Select your analysis.
4. In the toolbar, choose Actual size, and then choose Fit to width.

Allow users to dynamically select columns in a table visual

To have dashboard viewers select the columns that QuickSight displays in a table visual, complete the following steps:

1. Open the Amazon Quick console.
2. In the navigation pane, choose Analyses.
3. Select your analysis.
4. Create a multi-value string parameter that lists available column names.
5. Add a parameter control linked to the parameter.
6. Add calculated fields for each optional column that either return the column value when the parameter includes that column name, or NULL.
7. Add the calculated fields to the table field wells.
8. Configure the table to hide columns with NULL values.

If you have a large number of optional columns, then use multiple table visuals with sheet-level controls. Or use the column selector feature in paginated reports.

Add search functionality to a table column

Complete the following steps:

1. Open the Amazon Quick console.

2. In the navigation pane, choose Analyses.

3. Select your analysis.

4. In the toolbar, choose Parameters.

5. Choose Add, and then take the following actions.
   For Name, enter SearchTerm.
   For Data type (Not alterable after creation), choose String.
   For Static default value, leave the field blank.

6. Choose Create.

7. In the Parameter added window, choose Control.

8. Set the Style to Text field.

9. Choose Add.

10. Create a calculated field that uses the contains() or locate() function to filter rows.
    Example calculated field:

    ifelse(locate(${SearchTerm}, {ColumnName}) > 0, "Match", "No Match")

11. Add a filter to the visual that shows only rows where the calculated field equals Match or where the SearchTerm parameter is empty.

Resolve data manipulation issues in table visuals

To resolve data manipulation issues in table visuals, take the following actions:

• To merge data from multiple tables into a single table visual, create a join in the dataset editor.

• To manipulate column values, create a calculated field in the dataset. For example, you can prepend a "0" to an ID column in the following example formula:

  concat("0", {rm_id})

• QuickSight supports custom images in table visuals through the field style options. To add custom images, see To show URLs as images in Using field styling.

• For tables that require different logical conditions to determine displayed values, use nested ifelse() calculated fields.
  Example logic:
  ifelse(condition1, value1, ifelse(condition2, value2, defaultValue))

• For complex cross-tab or pivot-style layouts, use a pivot table visual.

Resolve column issues in paginated reports

If you use paginated reports and experience issues when you update dataset columns, then the table in the analysis references a removed column. To resolve this issue, take the following actions:

• Before you delete columns from the dataset, remove the columns from the visuals that reference them.
• To control the columns that you display in the report, use parameters and calculated fields.
• If you want the dataset columns to dynamically appear, then use a custom SQL query in the dataset that explicitly lists the columns.