Pivot / Cohort Table Chart (2.0)

Early-access feature

This new chart type is released as early-access, if you would like to try it out, please get in touch with our Support team at support@cluvio.com. See the Early Access section below for specifics of usage during the early access period.

The new Pivot Table transforms your query results into organized, hierarchical tables with multiple dimensions and aggregated values. This chart type helps you analyze complex datasets by grouping data into rows and columns with extensive customization options.

The Pivot Table 2.0 supports multiple levels of row and column hierarchies, value aggregation, conditional formatting, custom sorting, and color mapping.

Key Features

• Hierarchical Structure: Multiple levels of row and column groupings
• Value Aggregation: Sum, average, count, min, max, median, and concatenation
• Format Detection: Automatic detection of data types with manual override
• Custom Sorting: Sort by column values or related columns (e.g., sort months chronologically)
• Data Formatting: Format numbers, dates, text, money, and bytes with precision control
• Conditional Formatting: Apply styles, colors, and icons based on data values
• Color Mapping: 8 built-in color schemes with custom value ranges and inversion
• Text Options: Text wrapping, width control, and alignment
• Totals: Individual total controls for each row and column series
• Series Management: Drag & drop interface with cross-category movement
• Performance: Handles large datasets efficiently

Early access

The new Pivot Table 2.0 replaces the legacy Pivot Table, with both versions still being available for the next several months. With the new Pivot Table 2.0 enabled for your organization, here are the key things to know:

• The functionality has been tested extensively and is at "Release candidate" level of stability, but there may still be bugs or missing capabilities - we appreciate any bug reports or comments at support@cluvio.com
• The new chart replaces the existing Pivot/Cohort table chart, but your existing pivot tables will continue to work exactly the same, your users will not be affected
• When you create a new report, the new Multi-level pivot table will be used as default, but you can still use Switch to legacy pivot table action to use the legacy version
• When you create or upgrade any reports to use the new pivot table, it will be visible to your users and embeds
• To convert any existing legacy pivot tables, use the Switch to new pivot table action in the report editor
• Try the new version on dashboards without converting by using Preview New Pivot Tables action from the dashboard dropdown menu
• Test on sharing links by adding &newPivotTables=true to the link/embed URL

Here are a few screenshots of the elements mentioned:

Data Requirements

Available for query results with at least 3 columns. Requires:

• At least 1 column for Rows: For row grouping (typically from GROUP BY clauses)
• At least 1 column for Columns: For column grouping (typically from GROUP BY clauses)
• At least 1 column for Values: Numerical or text data for cell values (often aggregated with SQL functions)

Supports any combination of text, numerical, and date column types, and works with both aggregated and non-aggregated data.

Typical SQL Pattern

SELECT
  dimension1,        -- Row grouping
  dimension2,        -- Column grouping
  SUM(metric_value), -- Aggregated values
  additional_column  -- Additional data for sorting or display
FROM your_table
GROUP BY dimension1, dimension2, additional_column
ORDER BY dimension1, dimension2

Interactive Features

• Drag & Drop: Move series between Rows, Columns, and Values
• Reordering: Change order within each category
• Swap Rows/Columns: Exchange row and column series
• Custom Naming: Edit series display names inline
• Column Resizing: Drag column borders to resize
• Responsive Layout: Auto-adjusts to different table sizes
• Scrolling: Navigate large tables smoothly

Configuration

General Settings

• Table Size (S, M, L, XL): Controls overall size of table elements
• Line Spacing (Compact / Normal / Loose): Controls the line height of rows in the table
• Show Column Series Names: Display or hide column series names in the header (useful when there are multiple series for context)

Series Configuration

The chart uses three types of series:

• Pivot Rows: Columns from SQL results that form rows
• Pivot Columns: Columns from SQL results that form columns
• Pivot Values: Columns from SQL results that provide cell values

The left configuration section allows you to arrange SQL query columns into rows/columns/values by dragging items. Unassigned columns appear in the Unused Columns section. The unused columns are not displayed on the table, but they can be used to sort rows or columns. For example, you can sort months chronologically by assigning a hidden month number column to control the order, or sort categories by a custom ranking column. To use an unused column for sorting, select it in the sorting options for the relevant row or column series.

For each of the Rows and Columns series, you can turn on totals/subtotals for multi-level aggregation of values.

• For rows series, the totals are the total of values in each column, top to down, shown in the section Column Totals
• For columns series, the totals are the total of values in each row, left to right, shown in the section Row Totals

The total is typically a sum of the values, but other aggregation functions like average, median, min, max and others are available, configurable in the Customize tab for each of the values series.

Customize Row and Column Series

Options:

• Custom Name: Override column name for display (click pencil icon to edit)
• Data Format: Text, number, money, date, or bytes
• Value Alignment: Left, center, or right alignment
• Sort Order: Ascending or descending
• Sort By Column: Sort by different column (e.g., sort months chronologically)

Customize Value Series

Aggregation Types:

• Sum: Add all values (default for numeric data)
• Average: Calculate arithmetic mean
• Count: Count non-null values
• Min/Max: Find minimum or maximum values
• Median: Calculate median value
• Concatenate: Join text values or unique dates

Options:

• Custom Name: Override column name for display
• Data Format: Number, money, bytes, text, or date
• Value Alignment: Left, center (default), or right alignment
• Null as Zero: Treat null values as zero (for numeric formats)
• Exclude from Totals: Exclude from total calculations (when multiple value series exist)

Color Mapping:

• Enable Color Values: Color cells based on their values
• Color Schemes: 8 built-in options including gradients and diverging scales
• Custom Value Ranges: Override min, mid, max values for color mapping
• Invert Colors: Reverse the color scale
• Auto Midpoint: Automatically applies midpoint color when all values are identical

Data Formatting Options

Text Formatting

Formatting as text.

• Text Wrapping: Enable for long content
• Maximum Width: Set pixel limits (50-1000px)

Number Formatting

Formatting of whole or decimal numerical values.

• Rounding: Enable with precision control (0-20 decimal places)
• Abbreviation: Show as K, M, B for large numbers
• Percentage: Display as percentage (values series only)

Money Formatting

Formatting of numerical values as currency.

• Currency Symbol: Custom currency symbols (€, $, £, etc.)
• Decimal Precision: Combined with rounding settings
• Abbreviation: Large amount abbreviation (K, M, B)
• Null as Zero: Handle null values (values series only)

Date/Time Formatting

Formatting of Date, Time or DateTime values using the format specified in the Organization Preferences.

• Date Format: Date/time, date only, time only, or relative
• Date Granularity: When Date is the selected format, you can customize how the date value is shown: manual selection of granularity (day, week, month, quarter, year), or automatic, based on applied aggregation filter

Bytes Formatting

Special formatting useful when the numerical values represent bytes.

• Size Units: Automatic scaling (KB, MB, GB, etc.)
• Binary vs Decimal: Toggle KiB (1024-based) vs KB (1000-based) calculation
• Precision: Combined with rounding settings
• Null as Zero: Handle null values (values series only)

Coloring by Value

With the Color Values option, value cells display background colors based on where their numerical values fall in the range between the smallest and largest values in the table. This helps highlight differences, trends, and outliers.

The Color Range dropdown offers these pre-defined options:

1. Blue Gradient: White to Blue
2. Green Gradient: White to Green
3. Red Gradient: White to Red
4. Red-Blue Diverging: Red → White → Blue
5. Red-Green Diverging: Red → White → Green
6. Blue-Green Diverging: Blue → White → Green
7. Traffic Light (RYB): Red / Yellow / Blue
8. Traffic Light (RYG): Red / Yellow / Green

• Invert Color Range: Reverses the color application
• Min/Mid/Max Value: Sets fixed ranges instead of using data min/max values. Values outside the range receive min/max colors accordingly.

Advanced Totals Configuration

For each Values series, you can further customize the aggregation function. The selected function is used both for calculating totals as well as processing duplicate values for each row/column intersection (normally not present when query uses GROUP BY to aggregate).

Aggregation: The dropdown allows selecting the aggregation function applied

• Sum: Sums up the values
• Avg: Mean/average of the values (sum of the values divided by number of values). Note that the totals applied across multiple levels do not simply do average-of-averages but each of the averages is the correct average across all values that contribute to that total
• Median: Median of the values (the value below/above half of the values lies). Same as with Average, the median is correctly based on the individual values for any level of totals
• Count: The number of values present
• Min/Max: The minimum/maximum of the values

Exclude from Totals: Exclude series from calculations (useful for textual values alongside numerical data)

Advanced Sorting

The rows and headers are always sorted in ascending or descending order. Since the way pivoting of the data works, there is no stable "natural" unsorted order that could be derived from the raw data.

The sorting defaults to using the value itself, shown as Value in the Sort By dropdown.

By using additional columns that are not displayed on the pivot table and selecting them in the dropdown, you can implement custom sorting logic for rows or columns, avoiding the need for numerical prefixing.

As an example of a common problem, sorting months by name does not produce logical ordering. Instead of using 1 - January, 2 - February, etc., you can produce an additional column with the month number and use that column as the Sort By column. This way the numbers are not visible and are only used for the sorting logic.

Example SQL query:

SELECT
  TO_CHAR(date_column, 'Month') as month_name,
  EXTRACT(MONTH FROM date_column) as month_number,
  SUM(value) as total
FROM your_table
GROUP BY month_name, month_number

Conditional Formatting

Conditional formatting works similarly to regular tables, with key differences:

• When used with Color Values, conditional formatting applies on top of value-based background colors
• Different value series can apply different rules
• Formatting can be applied to Row or Column headers at each level independently

Performance and Limitations

No hard limit exists for the number of series in columns/rows/values, but practical limits apply based on readability and performance.

The final pivot table is limited to 500 columns.

Performance depends on configuration complexity, dataset size, and the user's device since pivoting calculations and rendering occur in the browser.

Examples

Sales Analysis by Region and Quarter

SELECT
  region,
  CONCAT('Q', EXTRACT(QUARTER FROM order_date)) as quarter,
  EXTRACT(QUARTER FROM order_date) as quarter_number,
  SUM(revenue) as total_revenue,
  COUNT(DISTINCT order_id) as order_count,
  AVG(order_value) as avg_order_value
FROM sales_data
WHERE order_date >= '2023-01-01'
GROUP BY region, quarter, quarter_number
ORDER BY region, quarter_number

Configuration:

• Rows: Region (text format, calculate totals enabled)
• Columns: Quarter (text format, sort by quarter_number column)
• Values:
  • Total Revenue (money format, sum aggregation, € symbol)
  • Order Count (number format, sum aggregation, abbreviation enabled)
  • Average Order Value (money format, average aggregation, color values with blue gradient)

Monthly Performance Dashboard

SELECT
  department,
  TO_CHAR(month, 'Mon YYYY') as period,
  month,
  SUM(target) as target,
  SUM(actual) as actual,
  ROUND((SUM(actual) / SUM(target) * 100), 1) as performance_pct
FROM performance_metrics
GROUP BY department, period, month
ORDER BY department, month

Configuration:

• Rows: Department (text format, calculate totals enabled)
• Columns: Period (text format, sort by month column)
• Values:
  • Target (number format, sum aggregation, abbreviation enabled)
  • Actual (number format, sum aggregation, abbreviation enabled)
  • Performance % (number format, percentage enabled, average aggregation, color values with red-green diverging)

Customer Cohort Analysis

SELECT
  customer_segment,
  acquisition_month,
  months_since_acquisition,
  COUNT(DISTINCT customer_id) as active_customers,
  SUM(revenue) as cohort_revenue
FROM customer_cohorts
GROUP BY customer_segment, acquisition_month, months_since_acquisition
ORDER BY customer_segment, acquisition_month, months_since_acquisition

Configuration:

• Rows: Customer Segment (text format), Acquisition Month (date format)
• Columns: Months Since Acquisition (number format)
• Values:
  • Active Customers (number format, sum aggregation, center alignment)
  • Cohort Revenue (money format, sum aggregation, color values with green gradient, custom min/max ranges)

Best Practices

Query Design:

• Always ORDER BY: Include ORDER BY clauses for consistent, predictable results
• Optimize GROUP BY: Place most selective dimensions first for performance
• Include Helper Columns: Add sorting columns for logical ordering (e.g., month numbers for chronological sorting)
• Pre-aggregate When Possible: Aggregate data in your query to improve performance

Configuration Strategy:

• Start Simple: Begin with basic row/column/value setup, add complexity incrementally
• Use Color Strategically: Apply color mapping to key metrics only to avoid visual overload
• Name Series Clearly: Use descriptive custom names for better user understanding
• Test Sorting Logic: Verify custom column sorting produces expected results
• Group Related Metrics: Keep related value series together for easier comparison

Performance Optimization:

• Choose Appropriate Formats: Use the most suitable format for each data type
• Limit Conditional Formatting: Excessive rules can impact rendering performance
• Consider Data Volume: Large datasets benefit from query-level aggregation
• Use Totals Judiciously: Enable totals only where they provide analytical value

User Experience:

• Consistent Alignment: Use consistent value alignment patterns across related series
• Logical Color Mapping: Ensure color schemes match user expectations (e.g., red for negative, green for positive)
• Clear Value Ranges: Set appropriate min/max values for color mapping to highlight meaningful differences
• Responsive Design: Test appearance across different table sizes (S, M, L, XL)