Pivot Table V2

Introduction

A pivot table is a powerful tool for summarizing and analyzing data. It allows you to aggregate data based on multiple dimensions and metrics, making it easier to identify trends and patterns.

Pivot Table V2 requires at least one group and a page limit higher than 100. Note that this refers to the pagination limit defined at the chart level, not the group limits defined at the Attribute level.

It supports up to three row groups and up to three column groups.

In the ChartFactor toolkit, we can define a Pivot Table V2 visualization in this way:

let provider = cf.provider("BigQuery");
let source = provider.source("bigquery-public-data:chicago_taxi_trips.taxi_trips");
let metric0 = cf.Metric("count");
let row0 = cf.Attribute("company")
    .limit(100)
    .sort("desc", cf.Metric());
let column0 = cf.Attribute("dropoff_community_area")
    .limit(50)
    .sort("desc", cf.Metric());
let myData = source.groupby(row0)
    .colgroupby(column0).metrics(metric0);
let color = cf.Color().theme("standard");
let myChart = myData.graph("Pivot Table V2")
    .set("color", color)
    .set("pinRows", false)
    .set("columnFilters", true)
    .set("dataZoom", "dragFilter")
    .set("showEmptyValuesAs", "-")
    .limit(100)
    .execute();

The code above creates a pivot table that summarizes the count of taxi trips by company and dropoff community area. The results are limited to the top 100 companies and the top 50 dropoff community areas, with a pagination limit of 100 records.

Page Size

Pivot Table V2 uses infinite scrolling to render data as naturally as possible. Behind the scenes, it uses pages of 100 rows by default. This page size can be changed with the limit() function as shown bellow:

let myChart = myData.graph("Pivot Table V2")
    .limit(200)
    .execute();

Page sizes under 100 are not allowed. This ensures the page size is big enough to be displayed in the view port of the Pivot Table V2.

Row totals and column totals

To render row and column totals, simply add the showTotal() call to your Attribute definitions. The example below renders totals for the company row group and for the payment_type columns group.

let provider = cf.provider("BigQuery");
let source = provider.source("bigquery-public-data:chicago_taxi_trips.taxi_trips");
let metric0 = cf.Metric("count");
let row0 = cf.Attribute("company")
    .limit(100)
    .showTotal()
    .sort("desc", cf.Metric());
let column0 = cf.Attribute("payment_type")
    .limit(50)
    .showTotal()
    .sort("desc", cf.Metric());
let myData = source.groupby(row0)
    .colgroupby(column0).metrics(metric0);
let color = cf.Color().theme("standard");
let myChart = myData.graph("Pivot Table V2")
    .set("color", color)
    .set("pinRows", false)
    .set("columnFilters", true)
    .set("dataZoom", "dragFilter")
    .set("showEmptyValuesAs", "-")
    .limit(100)
    .execute();

The previous code creates a pivot table shown below.

Configuration options

autoSizeColumns

A boolean value that indicates whether the columns should be automatically sized based on their content. Example:

let myChart = myData.graph("Pivot Table V2")
    .set("autoSizeColumns", true)
    .limit(100)
    .execute();

color

This setting allows to specify out-of-the-box color themes and also to override them. Please refer to the color themes for table visualizations documentation for details. Example:

let color = cf.Color().theme("standard");
let myChart = myData.graph("Pivot Table V2")
    .set("color", color)
    .limit(100)
    .execute();

columnFilters

A boolean value that indicates whether column filters should be enabled. Default is false. Example:

let myChart = myData.graph("Pivot Table V2")
    .set("columnFilters", true)
    .limit(100)
    .execute();

columnsWidth

This setting allows to manually set the ideal width for every grid column. Example:

let myChart = myData.graph("Pivot Table V2")
    .set("columnsWidth", [
        { "0": 323 },
        { "1": 158 },
        { "2": 167 },
        { "3": 167 },
        { "4": 153 },
        { "5": 153 },
        { "6": 153 },
        { "7": 153 },
        { "8": 153 },
        { "9": 153 },
        { "10": 153 },
        { "11": 153 },
        { "12": 153 }
    ])
    .limit(100)
    .execute();

dataZoom

A string that specifies the type of data zoom filter to be applied. It supports only dragFilter, default is null. Example:

let myChart = myData.graph("Pivot Table V2")
    .set("dataZoom", "dragFilter")
    .limit(100)
    .execute();

pinRows

A boolean value that indicates whether the rows should be pinned to the left side of the table. Default is true. example:

let myChart = myData.graph("Pivot Table V2")
    .set("pinRows", false)
    .limit(100)
    .execute();

rowHighlight

A boolean value that indicates whether the rows should be highlighted when hovered over. Default is true. Example:

let myChart = myData.graph("Pivot Table V2")
    .set("rowHighlight", false)
    .limit(100)
    .execute();

showEmptyValuesAs

A string that specifies how empty values should be displayed in the pivot table. It can be set to any string value, such as "-" or "N/A". Default is null, which means empty values will not be displayed. Example:

let myChart = myData.graph("Pivot Table V2")
    .set("showEmptyValuesAs", "my_custom_string_empty_values")
    .limit(100)
    .execute();

sizeColumnsToFit

A boolean value that indicates whether the columns should be resized to fit the available width of the pivot table. This configuration overrides the autoSizeColumns property. Example:

let myChart = myData.graph("Pivot Table V2")
    .set("sizeColumnsToFit", true)
    .limit(100)
    .execute();