Visualizations → Aggregated Table

About an Aggregated Table Visualization

An Aggregated Table visualization provides a tabular view of your data with the ability to utilize aggregated granularity. Aggregation allows you to change the granularity of the data provided in the table. You are able to utilize aggregations such as sum, average, min, etc. You can configure various properties for measures, dimensions, filters, and settings for the Aggregated Table insight.

Configurable Properties of an Aggregated Table Insight in the Analyzer

The available trays for an Aggregated Table insight within the Insight panel are:

  • Grouping Dimension: Add a column or formula to this tray to group the Aggregated Table data.
  • Measure: Add a column or formula to this tray to include it as a column in the Aggregated Table.
  • Individual Filter: Add a column or formula to this tray to filter individual dimension values in your Aggregated Table.
  • Aggregated Filter: Add a column to this tray to filter measures by aggregate values in your Aggregated Table.
  • Distinct Filter: Add a column to this tray to filter your Aggregated Table to show only distinct rows. This tray is available only when there is not a column or formula in the Grouping Dimension tray. Distinct Filters do not have pill properties in the Analyzer.
  • Sort By: Add a column or formula to this tray to sort the Aggregated Table. This tray is available only when there is not a column or formula in the Grouping Dimension tray.

A column or formula in a tray is a Pill. Each pill has configurable properties. The parent tray determines the available properties of a pill. Select the arrow to the right of the pill name to view the Properties panel and set the pill properties.

Aggregated Table Measure Pill Properties

Here are the Aggregated Table properties for the pills in the Measure tray:

Property Control Description
Data → Aggregation drop down list Select the type of aggregation to use on the measure property. The available aggregation types are: AVERAGE, COUNT, DISTINCT, MEDIAN, MIN, MAX, SUM, and NONE
Data → Scale drop down list Select the scale of the data. The available scales are: Percent (%), Thousands (K), and Millions (M)
Data → Running Total toggle Enable this property to have the measure value calculated as a running total rather than a simple summation
Data → Filter drag and drop Drag and drop a column or formula to create a filter for the measure values.
Format → Basic Formatting select Select a basic formatting type: No Format, Decimal, Rounded, Dollar, Dollar Rounded, Euro, Euro Rounded, Yen, Yen Rounded, Percent, and Percent Rounded
Format → Decimal Places text box Enter the displayed decimal value on non rounded formats
Format → Thousands Separator toggle Enable this property to include comma separated thousands in values
Format → Conditional Formatting link Select + Add Conditional Format to change the color of a data point based on its value
Conditional Formatting → Operator drop down list Select the operator to use in the conditional formatting rule
Conditional Formatting → Value text box Enter the threshold value of the conditional formatting rule
Conditional Formatting → Background select Select the background color to apply when the conditional formatting rule is true
Conditional Formatting → Text Color select Select the text color to apply when the conditional formatting rule is true
Drill Down → Dashboards Drill down link Select + Add Dashboard to select a dashboard tab to drill down to when the user clicks on the measure value in the table column
Add Dashboard → Include Runtime Filters toggle Enable this property to pass a filter from one dashboard to another
Add Dashboard → Search Dashboard text box Enter a search string to filter the dashboard tree. From the results, select a dashboard tab to drill down to.
Advanced → Base Field drag and drop Drag and drop the column to join two tables that are not directly joined. See the Base Table Concept for more detail.
Advanced → Query Plan button Select this property to reveal the direct data map path that the Incorta Analytics Service uses to retrieve or calculate the measure

Aggregated Table Grouping Dimension Pill Properties

Here are the Aggregated Table properties for the pills in the Grouping Dimension tray:

Property Control Description
Data → URL text box Enter the URL to open in the browser when the user clicks on the grouping dimension value in the table (begin with http:// or https://)
Data → Show Subtotal toggle Enable this property to show the subtotal of the measure compared to the dimension. It is only available when using multiple dimension pills.
Data → Date Part drop down list This property is visible for date data type dimensions. Select the format to display the date value
Data → Show Empty Groups toggle Enable this option to display table rows for which the grouping dimension contains a null value
Data → Sort By drag and drop Drag and drop the column or formula to sort the table by
Drill Down → Dashboards Drill Down link Select + Add Dashboard to select a dashboard tab to drill down to when the user clicks on the grouping dimension value in the table
Add Dashboard → Include Runtime Filters toggle Enable this property to pass a filter from one dashboard to another
Add Dashboard → Search Dashboard text box Enter a search string to filter the dashboard tree. From the results, select a dashboard tab to drill down to.

Aggregated Table Individual Filter Properties

Here are the Aggregated Table properties for the pills in the Individual Filter tray:

Property Control Description
Operator drop down list Select the operator to use in the filter criteria
Values multi-select Select the value(s) to filter the table data by. Enter a string in the search bar to filter the list of values. To add a value to the list, enter it in the text box below the list and select +.

Aggregated Table Aggregate Filter Properties

Here are the Aggregated Table properties for the pills in the Aggregate Filter tray:

Property Control Description
Aggregation drop down list Select the aggregation type to use in the filter. The available aggregation types are: AVERAGE, COUNT, DISTINCT, MEDIAN, MIN, MAX, SUM, and NONE
Date Part drop down list This property is visible when using a date date type column. Select the format to display the date value
Operator drop down list Select the operator to use in value comparison to the measure value
Values multi-select, text box Select the comparison value to use in the filter, or enter a new value to use in filter comparison

Aggregated Table Insight Settings

Here are the Aggregated Table properties for the Insight Settings that appear when you select the Settings (gear icon) in the Action bar.

Property Control Description
General → Page Size text box Enter the number of rows per page
General → Max Rows Limit text box Enter the maximum number of rows to include in the table
General → Auto Refresh toggle Enable this property to automatically update the insight when the underlying data is updated
General → Merge Rows toggle Enable this property to merge rows in the Aggregated Table insight creating a simplified visualization
General → Merge Columns toggle Enable this property to merge columns in the Aggregated Table insight creating a simplified visualization
General → Dynamic Group-by toggle Enable this property to allow the dashboard users to select a grouping dimension from the insight action menu
General → Dynamic Measures toggle Enable this property to have the dashboard render a specified measure. This feature is useful when the table has more than one measure
Dynamic Measures → Dynamic Measures Default toggle Select a single measure, the selected measure will be the only displayed measure on the dashboard. This will create a simplified view for the user. Default value is all measures.
General → Subtotal toggle Enable this property to include a subtotal at the end of each group
General → Total toggle Enable this property to include a total for the entire table
Layout → Fix Column(s) text box Enter the number of columns to keep visible when you scroll to the right
Layout → Headers toggle Enable this property to display column names
Layout → Transpose toggle Enable this property to convert the table columns to rows and the table rows to columns
Advanced → Max Groups text box Set the maximum number of groups that cannot be exceeded in the Insight
Advanced → Missing Value Text text box Enter the string to display when a column is missing data
Advanced → Join Measures drop down list Select Yes to calculate the group-by function across joined tables.
Warning

There is an impact to engine performance when you select Yes to enable the Join Measures property.

Create an Aggregated Table Insight Visualization

For an existing dashboard, to create an aggregated table insight for a given dashboard tab, follow these steps:

  • If not already open, open a dashboard.
  • To add a new insight to the dashboard, in the Action bar, select +.
  • If needed, in the Analyzer, in the Data panel, select Add Data Set.
  • In the Manage Data Sets panel, select one or more business schema views and/or one or more physical schema tables.
  • To close the Manage Data Sets panel, select X or any other area of the Analyzer.
  • In the Insight panel, select V. Under Tables, select Aggregated Table.
Important

If the data in a column is Null, this can indicate a missing join, or joins in the wrong direction.

Aggregated Table Insight Example

Create an Aggregated Table insight that shows product revenue by country in several high value markets.

In the Cluster Management Console (CMC), you can create a tenant that includes Sample Data. The Sample Data includes the SALES and date schemas. This example uses the SALES schema.

Add an insight

  • In the Action bar, select +.
  • In the Data panel, select Add Data Set.
  • In the Manage Data Sets panel, in Tables, select the SALES schema.
  • Close the Manage Data Sets panel.
  • In the Insight panel, under Tables, select Aggregated Table.
  • From the Data panel, drag and drop Revenue from the SALES table to the Measure tray.

    • Edit the Revenue format property to Dollar Rounded.
  • From the Data panel, drag and drop Product from the Products table to the Grouping Dimension tray.
  • From the Data panel, drag and drop Country from the Countries table to the Grouping Dimension tray.
  • From the Data panel, drag and drop Country from the Countries table to the Individual Filter tray.

    • Select France, Germany, and Netherlands for the filter Values in the Country filter. The Operator type should be set to In.
  • From the Data panel, drag and drop Product from the Products table to the Individual Filter tray.

    • Select Product names 128MB Memory Card, 256 Memory Card, and 8.3 Minitower Speakers in the filter Values. The Operator type should be set to In.
  • From the Data panel, drag and drop Revenue from the SALES table to the Aggregate Filter tray.

    • Edit the Revenue filter to Aggregation type SUM, operator type Greater Than, and add a value of 70,000 to filter by.
  • From the Settings menu (gear icon), toggle Merge Rows.
  • Name the insight Product Revenue in High Value European Markets.
  • Select Save.

Available User Interactions for a Aggregated Table Insight on a Dashboard Tab

As a dashboard end user, there are several actions you can take to customize the Aggregated Table insight to fit your specific needs.

Sort the Aggregated Table Insight

Select a column header to sort the Aggregated Table insight descending (down arrow) or ascending (up arrow).

Page through the Aggregated Table Insight

Select the arrows at the bottom of the Aggregated Table insight to page forward (right arrow), backward (left arrow), to the end of the table (double right arrow), and the beginning of the table (double left arrow). The Page Size is controlled in Insight Settings.

Copy and Paste Aggregated Table Insight Cells

Select Aggregated Table insight cells to copy them from the table and paste them into another application.

Personalize the Aggregated Table Insight Columns

Use the Edit Layout panel to select the columns to include in the Aggregated Table insight with the following steps:

  • Open the Edit Layout panel with one of these two options:

    • Select Personalize (Page icon) in the upper right corner of the dashboard page, and Edit Insight Settings in the Context bar.
    • Select More Options (⋮ vertical ellipsis icon) → Personalize to the right of the tab name on the active dashboard tab, and Edit Insight Settings in the Context bar.
  • Select the Aggregated Table insight on the dashboard.
  • In the Table Columns panel, select or deselect the measures to include in the insight. All measures are included by default.
  • Select the Save button.

Download the Aggregated Table

  • Select More Options (⋮ vertical ellipsis icon) for the Aggregated Table insight.
  • Select Download.
  • Select the download file format:

    • XLSX
    • CSV

Set an Alert when Data in the Aggregated Table Insight Changes

  • Select More Options (⋮ vertical ellipsis icon) for the Aggregated Table insight.
  • Select Set Alert. Refer to the Scheduler document for additional information on Data Alerts.