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 → 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 (kebab 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 (kebab 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 (kebab icon) for the Aggregated Table insight.
Select Set Alert. Refer to the Scheduler document for additional information on Data Alerts.