Skip to main content

Pivot / Cohort Table Chart

A pivot / cohort table chart displays report query results in a table that focuses the viewer's attention on the values of a particular result column, which are typically the result of an SQL aggregate function, with other result columns being used for the table axes.

Pivot / Cohort table chart

A pivot / cohort table supports automatic calculation of row and column totals, rich value formatting options and data-driven color highlighting. See Configuration.

Data Requirements​

The Pivot / Cohort chart is available if a report's query result contains at least 3 columns of values with at least one of them being numerical. By default, the first numerical column that has a different value in each row is chosen as the Value Column, i.e. the metric displayed in the table cells, whereas columns used in the GROUP BY clause are used for the table axes. A typical SQL query for a Pivot / Cohort table chart uses a GROUP BY expression for the 2 columns to use as the table axes and an aggregation function to produce the numerical values shown in the table. See Configuration and Examples.

Interactive Features​

A Pivot / Cohort chart is not interactive.

Configuration​

The Pivot / Cohort chart configuration provides the following general options:

Pivot Table Config Pivot Table Config

  • S, M, L, XL: Preset sizes for the table content and axis labels.
  • Sort Rows: Whether the rows should be sorted by the row labels (in ascending order).
  • Sort Columns: Whether the columns should be sorted by the column labels (in ascending order).
  • Row Totals: Whether row totals should be computed and shown in an additional column.
  • Column Totals: Whether column totals should be computed and shown in an additional row.
  • Color Values: Whether the table cells should be colored based on the values shown. If enabled, the Color Options allow configuring the color palette.

Columns​

The Columns section configures the use of the result columns in the chart. The columns used as Rows and Columns should usually appear in a GROUP BY clause of your SQL query, whereas the column used for the Value (in the table cells) should contain values produced by an SQL aggregate function (e.g. SUM).

image-300 image-300

Value Formatting​

The Value Formatting section configures the formatting of the values in the table cells.

image-300 image-300

Color Options​

The Color Options section configures the appearance of the table cells, if Color Values is enabled. The coloring is determined by which is the magnitude of the displayed value. The predefined color palettes offer a variety of color schemes, where each scheme shows a gradient from left (lower values) to right (higher values). Select Advanced to tune the numeric bounds used for coloring.

image-300 image-300

Value Aggregation​

If your query result data contains multiple rows with the same values for columns selected as Rows and Columns (i.e. the columns used for grouping), a table cell effectively has more than a single value. With the Value Aggregation configuration, you can choose how these values are aggregated. Click on the information icon for further details on this configuration option.

image-300 image-300

Align Values​

In this section you can configure the alignment of the values in the table cells.

image-200 image-200

Conditional Formatting​

The Conditional Format configuration allows configuring cell formatting rules based on conditions on values of any result column, analogous to the conditional formatting in the regular table chart.

image-300 image-300

Examples​

The following example calculates the total number of flights per month and day of week on Cluvio's sample data:

SELECT TO_CHAR({dep_timestamp:month}, 'Month'), day_of_week, COUNT(1)
FROM flights
GROUP BY 1,2
ORDER BY 1,2

Selecting the Pivot / Cohort chart yields:

Pivot Table Sample 1 Pivot Table Sample 1

Note how Cluvio automatically preconfigured the columns to use for Rows, Columns and Value in the chart configuration.