Skip to content

Pivot Table

A pivot table is a table that summarizes data in another table, and is made by applying an operation such as sorting, averaging, or summing to data in the first table, typically including grouping of the data.

In the ChartFactor toolkit, we can define a Pivot Table in the following way:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
let metrics = [];

metrics.push(cf.Metric('commission', 'avg'));
metrics.push(cf.Metric('qtysold', 'sum'));
let chart = cf.provider('Elasticsearch')
    .source('Ticket Sales')
    .rows(
        cf.Row('venuestate', 'Venue State'),
        cf.Row('venuecity', 'Venue City').sort('asc'),
        cf.Row('venuename', 'Venue Name')
    )
    .columns(
        cf.Column('catgroup'),
        cf.Column('catname').sort('asc')
    )
    .metrics(...metrics)
    .graph('Pivot Table')
    .element('chart')
    .execute()

The previous code produces the following result: PT

The pivot query syntax is very similar to the raw query syntax but instead of fields it has rows and columns. They can be declared in a short way in the following way:

1
.rows('venuestate','venuecity', ...)

and in a long way like the following:

1
2
3
4
.rows(
    cf.Row('venuestate', 'Venue State'),
    cf.Row('venuecity', 'Venue City').sort('asc')
)

In the long form for rows you can specify a label as a second argument and a sort order for rows and columns. At the moment, the queries only admit one ordering criterion by the rows and metrics and another by the columns.

Configuring the size of the queries

The limit, climit, offset, and coffset settings are provider-specific and only supported by the Zoomdata provider.
limit: the number of rows of the final result of the pivot query (the amount of data needed to assemble it is always greater). For queries without a visualization (dummy chart), the default limit is 1000. For queries with a visualization, the default limit is 100. For the Pivot table visualizaiton, it represents the number of rows in a page with infinite scrolling support. As users scroll down or up, the table will bring 100 more rows using the data provider. climit: the number of columns of the final result of the pivot query offset: the row number where to start (needed for row server scrolling) coffset: the column number where to start (needed for column server scrolling)

The renderLimit setting is supported across providers. It controls the size of the result set passed to the visualization and it is useful when the the result set is too big to be rendered in a performant way by the browser or device. The default value for renderLimit is 20,000.

The example below illustrates limit for rows and columns and offset for rows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
let metrics = [];
metrics.push(cf.Metric('qtysold', 'sum'));
let chart = cf.provider('Elasticsearch')
    .source('Ticket Sales')
    .rows('venuestate', 'venuecity')
    .columns('catgroup', 'catname')
    .metrics(...metrics)
    .limit(10)        // row limit
    .climit(10)       // column limit
    .offset(5)        // row offset
    .coffset(0)       // column offset
    .renderLimit(200) 
    .graph('Pivot Table')
    .element('chart')
    .execute()

Setting time granularity

You can specify the time granularity of a Row or Column as shown bellow. If not specified, it will use the default granularity of that time field at the data provider level.

1
2
let row = cf.Row('@timestamp').func('DAY');
let column = cf.Column('@timestamp').func('YEAR');

Auto size columns

The autoSizeColumns setting will make all the visible columns on the table viewport adjust to the text.

1
    .set('autoSizeColumns', true)

Custom metadata

The Pivot Table supports custom metadata in the same way that other visualizations, to see details click here

Providers Support

Please refer to the Providers Capabilities table.