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:

PropertyControlDescription
Data → Aggregationdrop down listSelect 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 → Scaledrop down listSelect the scale of the data. The available scales are: Percent (%), Thousands (K), and Millions (M)
Data → Running TotaltoggleEnable this property to have the measure value calculated as a running total rather than a simple summation
Data → Filterdrag and dropDrag and drop a column or formula to create a filter for the measure values.
Format → Basic FormattingselectSelect a basic formatting type: No Format, Decimal, Rounded, Dollar, Dollar Rounded, Euro, Euro Rounded, Yen, Yen Rounded, Percent, and Percent Rounded
Format → Decimal Placestext boxEnter the displayed decimal value on non rounded formats
Format → Thousands SeparatortoggleEnable this property to include comma separated thousands in values
Format → Conditional FormattinglinkSelect + Add Conditional Format to change the color of a data point based on its value
Conditional Formatting → Operatordrop down listSelect the operator to use in the conditional formatting rule
Conditional Formatting → Valuetext boxEnter the threshold value of the conditional formatting rule
Conditional Formatting → BackgroundselectSelect the background color to apply when the conditional formatting rule is true
Conditional Formatting → Text ColorselectSelect the text color to apply when the conditional formatting rule is true
Drill Down → Dashboards Drill downlinkSelect + 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 FilterstoggleEnable this property to pass a filter from one dashboard to another
Add Dashboard → Search Dashboardtext boxEnter a search string to filter the dashboard tree. From the results, select a dashboard tab to drill down to.
Advanced → Base Fielddrag and dropDrag and drop the column to join two tables that are not directly joined. See the Base Table Concept for more detail.
Advanced → Query PlanbuttonSelect 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:

PropertyControlDescription
Data → URLtext boxEnter 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 SubtotaltoggleEnable this property to show the subtotal of the measure compared to the dimension. It is only available when using multiple dimension pills.
Data → Date Partdrop down listThis property is visible for date data type dimensions. Select the format to display the date value
Data → Show Empty GroupstoggleEnable this option to display table rows for which the grouping dimension contains a null value
Data → Sort Bydrag and dropDrag and drop the column or formula to sort the table by
Drill Down → Dashboards Drill DownlinkSelect + 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 FilterstoggleEnable this property to pass a filter from one dashboard to another
Add Dashboard → Search Dashboardtext boxEnter 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:

PropertyControlDescription
Operatordrop down listSelect the operator to use in the filter criteria
Valuesmulti-selectSelect 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:

PropertyControlDescription
Aggregationdrop down listSelect the aggregation type to use in the filter. The available aggregation types are: AVERAGE, COUNT, DISTINCT, MEDIAN, MIN, MAX, SUM, and NONE
Date Partdrop down listThis property is visible when using a date date type column. Select the format to display the date value
Operatordrop down listSelect the operator to use in value comparison to the measure value
Valuesmulti-select, text boxSelect 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.

PropertyControlDescription
General → Page Sizetext boxEnter the number of rows per page
General → Max Rows Limittext boxEnter the maximum number of rows to include in the table
General → Auto RefreshtoggleEnable this property to automatically update the insight when the underlying data is updated
General → Merge RowstoggleEnable this property to merge rows in the Aggregated Table insight creating a simplified visualization
General → Merge ColumnstoggleEnable this property to merge columns in the Aggregated Table insight creating a simplified visualization
General → Dynamic Group-bytoggleEnable this property to allow the dashboard users to select a grouping dimension from the insight action menu
General → Dynamic MeasurestoggleEnable 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 DefaulttoggleSelect 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 → SubtotaltoggleEnable this property to include a subtotal at the end of each group
General → TotaltoggleEnable this property to include a total for the entire table
Layout → Fix Column(s)text boxEnter the number of columns to keep visible when you scroll to the right
Layout → HeaderstoggleEnable this property to display column names
Layout → TransposetoggleEnable this property to convert the table columns to rows and the table rows to columns
Advanced → Max Groupstext boxSet the maximum number of groups that cannot be exceeded in the Insight
Advanced → Missing Value Texttext boxEnter the string to display when a column is missing data
Advanced → Join Measuresdrop down listSelect 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 Manage Dataset.
  • 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 Manage Dataset.
  • 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.