Configuring fields in data tables

This guide explains how to transpose dimensions in a data table, add conditional formatting to data in the data table, and visualize metrics in the data table as a heatmap, sparkline, or bars.

Introduction

A data table in a widget is created from dimensions, metrics, and calculated KPIs. Select at least one dimension and one metric to create a table.

This guide explains how to perform the following actions to configure the fields in a data table:

• Transpose dimensions in a data table

• Add calculations for a metric to a data table

• Add conditional formats to metrics

• Visualize metrics in a table as a heatmap, sparkline, or bars

All changes you make to a data table are applied and saved immediately. For more information on configuring the basic settings for data tables in Explore, see Configuring data tables.

Prerequisites

Before you complete the procedure in this guide, perform all of the following actions:

• Load your collected data into Explore & Present.

• Select the data you want to add to the widget.

Transposing dimensions

Transposing a dimension converts the values within the dimension from rows into columns. Transposed dimensions are displayed as sub-columns underneath each metric. You can only transpose dimensions in tables that contain at least two dimensions and at least one metric.

To transpose a dimension in a data table, follow these steps:

1. Go to the Explore page.

2. At the top of the page, select the View tab that contains the widget.

3. In the Dimensions section of the view, drag the dimension you want to transpose to the furthest right position.

4. Click the dimension you want to transpose.

5. In the drop-down menu, click Transpose.

As a result, the values for the transposed dimension will appear in the table as sub-columns.

Add calculations for a metric to a data table

Table calculations allow you to perform calculations on the metric values in a table, and display the results of these calculations in the table. You cannot use table calculations if the data in the table is sorted. You can add multiple table calculations to a metric, and apply table calculations to more than one metric in the data table.

The following calculation types are available:

• Running total - displays the running total as a value or percentage, calculated at the dimension level

• Percent of total - displays the percentage of the total value that each row represents

• Difference of total - displays the difference between the total value and the value of each row as a value or percentage

• Running comparison - compares each value with other rows in the table at the dimension level

• Rolling average - displays the rolling average calculated at the dimension level

To use table calculations for a metric in your data table, follow these steps:

1. Go to the Explore page.

2. At the top of the page, select the View tab that contains the widget.

3. In the Metrics section of the view, click the metric for which you want to perform table calculations.

4. In the drop-down menu, click Table calculation.

5. In the Table calculation window, select the table calculation type and configure the corresponding settings.

   Table calculations replace the column containing the metric values with a column containing the calculated values. To include the original values and table calculation in your table, re-add the original column containing the metric values by selecting the metric in the Dimensions & Metrics list on the left.

   None

   Select this option to remove table calculations for the selected metric.

   Running total

   • In Start at, select whether to start the table calculation from the first or last row.

   • In Display as, select whether to display the running total as a value or a percentage of the total.

   Percent of total

   • In Calculate for, select whether to calculate the percentage value for all dimensions or only the lowest-level dimension.

   Difference of total

   • In Display as, select whether to display the difference as a value or a percentage.

   Running comparison

   • In Display as, select whether to display the comparison as a value, percentage or index.

   • In Compare with, select the row to which you want to compare each value.

   • In Hide, select whether you want to hide the first or last row, or none.

   • (Optional) Select the Show only inner-most dimension values checkbox to only show the comparison for the lowest-level dimension.

   Rolling average

   • In Number of previous datapoints included and Number of next datapoints included, select how many previous and following rows you want to use to calculate the average.

     For example, if you select 1 in both of these fields, the average is calculated for each value using the previous, current and following row.

6. Click Apply.

As a result, the selected table calculation will be applied to the selected metric.

Adding conditional formats to metrics

Conditional formats allow you to apply custom formatting to metric values that satisfy defined conditions. The conditions are checked in order, from top to bottom. This means that later conditions will override previous conditions, even if the value meets both conditions.

To add a conditional format to a metric in the data table, follow these steps:

1. Go to the Explore page.

2. At the top of the page, select the View tab that contains the widget.

3. In the Metrics section of the view, click the metric to which you want to add conditional formatting.

4. In the drop-down menu, click Conditional format.

5. In the Conditional format window, click + Add conditional format.

6. In the first drop-down menu, select the condition that the metric needs to meet, such as > (greater than).

7. In the second drop-down menu and the third field, choose the reference on which you want to base the conditional format:

   • To compare the selected metric to a fixed value, select Value and enter the value in the next field.

   • To compare the selected metric to another metric, select Metric and choose the metric to use as a reference.

8. Set the conditional format that will be applied if the metric satisfies the condition. To do this, use the settings listed below.

   • Font color

   • Background color

   • Bold text

   • Indicator

   The Example box shows what the metric will look like when the conditional format is applied.

9. (Optional) To apply the formatting to the Total row, select the Conditional formatting for the total row checkbox.

10. (Optional) To add another conditional format to the same metric, click + Add conditional format.

11. Click Apply.

As a result, the conditional formatting will be applied to all metrics that meet the defined condition.

Visualizing metrics in a data table

You can visualize metric values in the data table in three ways - as a heatmap, a sparkline chart, and as bars.

To visualize a metric as a sparkline chart, the data table must meet the following conditions:

• The last dimension needs to be one of the following time dimensions: Daily, Weekly, Monthly, Quarterly, Yearly.

• The last dimension needs to be transposed. For more information, see Transposing dimensions.

• The columns are grouped by metrics (this is the default option after the last dimension is transposed).

• The transposed dimension is not the only dimension.

To visualize a metric in any of these forms in the data table, follow these steps:

1. Go to the Explore page.

2. At the top of the page, select the View tab that contains the widget.

3. In the Metrics section of the view, click the metric that you want to visualize in the data table.

4. In the drop-down menu, select how you want to visualize the metric and configure the corresponding settings, as explained below.

   Visualize the selected metric as a heatmap

   Select Heatmap, then configure the following settings:

   • Select the Minimum is zero checkbox to use 0 as the minimum value in the heatmap.

     Deselect this checkbox to use the lowest value of the metric as the minimum value.

   • Select the Show value checkbox to display the metric values in the heatmap.

     Deselect this checkbox to only display a heatmap, without the metric values.

   • In the Colors section, choose the colors to use in the heatmap:

     • Select the Simple gradient checkbox to only use two colors in your heatmap.

     • Select the Reverse color checkbox to change how the colors are assigned to the metric values. The colors are shown in the order they will be assigned, from lowest to highest values.

     • Click Reset colors to reset the colors to the Adverity default colors.

   To remove the heatmap visualization for a metric, deselect the Active checkbox.

   Visualize the selected metric as a sparkline

   Select Visualize as sparkline, then configure the following settings:

   • Choose the type of line you want to use to visualize the metric values.

   • Choose the color you want to use for the line.

   To remove the sparkline visualization for a metric, in the Metrics section of the view, click the metric and click Revert.

   Visualize the selected metric as bars

   Select Visualize as bars. There are no additional settings for this visualization option.

   Each bar shows the metric value as the darker portion of the bar. The highest metric value represents 100% of the bar.

   To remove the bar visualization for a metric, in the Metrics section of the view, click the metric and click Revert.

5. Click Apply.

As a result, the metric will be displayed as a heatmap, sparkline, or bars within the data table. The images below show examples of each of these visualization options applied to the same data set.