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

If you are building a query to obtain data without visualization (dummy chart), the limit is the number of rows of the final result of the pivot table (the amount of data needed to assemble it is always greater). If this number is not specified, the default limit is 1000. If it is a query to build a visualization, the default limit is 100 but this limit will only limit the size of the pages of the infinite-scrolling table. As users scroll down or up, the table will bring 100 more rows using the data provider. 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
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
    .graph('Pivot Table')
    .element('chart')
    .execute()

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.