Column Calculations

Overview

Column calculations can be used to add a row of calculated values to the top or bottom of your table to display information such as the sum of a columns data.

Source Code

HTML

<div id="example-table"></div>

JavaScript

var table = new Tabulator("#example-table", {
height:"311px",
movableColumns:true,
columns:[
{title:"Name", field:"name", width:200},
{title:"Progress", field:"progress", width:100, sorter:"number", bottomCalc:"avg", bottomCalcParams:{precision:3}},
{title:"Gender", field:"gender"},
{title:"Rating", field:"rating", width:80, bottomCalc:"avg"},
{title:"Favourite Color", field:"col"},
{title:"Date Of Birth", field:"dob", align:"center", sorter:"date"},
{title:"Driver", field:"car", align:"center", formatter:"tickCross", topCalc:"count"},
],
});

There are two options that can be set in a column definition object to define a calculation, the topCalc option defines a calculation for the top of the column, and the bottomCalc defines a calculation for the bottom of the column. You can define, either, both or neither of the options.

{title:"Rating", field:"rating", topCalc:"avg"} //show average rating

Calculation Functions

Built In Functions

Tabulator comes with a number of preconfigured calculations. In the examples below the topCalc and topCalcParams options are used, but the exact same functionality is also available for the bottomCalc and bottomCalcParams options.

Note: For a guide to adding your own calculations to this list, have a look at the Extending Tabulator section.

Average

The avg function calculates the average value of all numerical cells in a column.

{title:"Example", field:"example", topCalc:"avg", topCalcParams:{
precision:1,
}}

The calculation has optional properties for the topCalcParams or bottomCalcParams objects:

• precision - the number of decimals to display (default is 2), setting this value to false will display however many decimals are provided with the number

Maximum

The max function displays the maximum value from all numerical cells in a column.

{title:"Example", field:"example", topCalc:"max", topCalcParams:{
precision:1,
}}

The calculation has optional properties for the topCalcParams or bottomCalcParams objects:

• precision - the number of decimals to display (default is 2), setting this value to false will display however many decimals are provided with the number

Minimum

The min function displays the minimum value from all numerical cells in a column.

{title:"Example", field:"example", topCalc:"min", topCalcParams:{
precision:1,
}}

The calculation has optional properties for the topCalcParams or bottomCalcParams objects:

• precision - the number of decimals to display (default is 2), setting this value to false will display however many decimals are provided with the number

Sum

The sum function displays the sum of all numerical cells in a column.

{title:"Example", field:"example", topCalc:"sum", topCalcParams:{
precision:1,
}}

The calculation has optional properties for the topCalcParams or bottomCalcParams objects:

• precision - the number of decimals to display (default is 2), setting this value to false will display however many decimals are provided with the number

Concatenate

The concat function joins the values of all cells in a column togeather as a string.

{title:"Example", field:"example", topCalc:"concat"}

Count

The count function counts the number of non empty cells in a column (cells that do not have a value of null, undefined or "").

{title:"Example", field:"example", topCalc:"count"}

Params Lookup Function

If you want to dynamically generate the calc params at the time the calculation is called you can pass a function into the property that should return the params object.

//define lookup function
function paramLookup(values, data){
//values - array of column values
//data - all table data

//do some processing and return the param object
return {param1:"green"};
}

//column definition
{title:"Rating", field:"rating", topCalc:customCalcFunction, topCalcParams:paramLookup}

Note: If a calculation is defined on any column the calculations rows will be shown, any column without a calculations will show as an empty cell in the calculation row

Custom Calculation Function

If you want to perform your own calculations on a column then you can pass a custom calculation function to either of the topCalc or bottomCalc options.

//count number of users over 18
var adultCalc = function(values, data, calcParams){
//values - array of column values
//data - all table data
//calcParams - params passed from the column definition object

var calc = 0;

values.forEach(function(value){
if(value > 18){
calc ++;
}
});

return calc;
}

//in your column definition for the column

Calculation Layout

Calculation Visibility

By default column calculations are shown at the top and bottom of the table, unless row grouping is enabled, in which case they are shown at the top and bottom of each group.

The columnCalcs option lets you decided where the calculations should be displayed, it can take one of four values:

• true - show calcs at top and bottom of the table, unless grouped, then show in groups (boolean, default)
• both - show calcs at top and bottom of the table and show in groups
• table - show calcs at top and bottom of the table only
• group - show calcs in groups only
var table = new Tabulator("#example-table", {
columnCalcs:"both", //show column calculations at top and bottom of table and in groups
});

Calculations For Grouped Data

When you are using the groupBy option with your table, the calculation rows will be displayed differently. Instead of being displayed at the top and bottom of the table, they will be displayed inside each group, at the top and bottom of that group, showing the calculations for that group.

Group Calculation Visibility

By default Tabulator will hide column calculations in groups when the group is toggled closed. If you would like column calculations to display when a group is closed, set the groupClosedShowCalcs option to true.

var table = new Tabulator("#example-table", {
groupClosedShowCalcs:true, //show column calculations when a group is closed
});

Formatting Calculation Cells

You can apply formatters (see Formatting Data for more information) to any calculation cells, using the topCalcFormatter and bottomCalcFormatter options in a columns definition object. you can also pass in additional parameters to the formatter functions using the topCalcFormatterParams and bottomCalcFormatterParams options:

{title:"Rating", field:"rating", topCalc:"avg", topCalcFormatter:"star", topCalcFormatterParams:{stars:5}} //apply star formatter to top calculation cell

Params Lookup Function

If you want to dynamically generate the formatter params object at the time the formatter is called you can pass a function into the property that should return the params object.

//define lookup function
function paramLookup(cell){
//cell - the cell component

//do some processing and return the param object
return {param1:"green"};
}

//column definition
{title:"Rating", field:"rating", topCalc:"avg", topCalcFormatter:"star", topCalcFormatterParams:paramLookup}

Calculation Results

You can retrieve the results of the column calculations at any point using the getCalcResults function.

var results = table.getCalcResults();

For a table without grouped rows, this will return an object with top and bottom properties, that contain a row data object for all the columns in the table for the top calculations and bottom calculations respectively.

{
top:{
age:53,
score:73
},
bottom:{
age:27,
score:66
},
}

For tables with grouped rows, this will return an object, where each group is represented by a property in the object, with the key for the group being the key for the property. As above each of the grouped objects contains a top and bottom property as well as a groups property that contains the data for any sub groups.

{
old:{
top:{
age:53,
score:73
},
bottom:{
age:95,
score:66
},
groups:{
//data for any sub groups
}
}
young:{
top:{
age:18,
score:73
},
bottom:{
age:12,
score:66
},
groups:{
//data for any sub groups
}
}
}