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.
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 Update
Calculations will be automatically updated whenever the row data is changed or edited.
If you want to trigger an update of the calculation at any point you can call the recalc function on the table.
table.recalc(); //recalculate all column calculations
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 together 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"}
Unique
The unique function counts the number of unique non-empty values in a column (cells that do not have a value of null, undefined or "").
{title:"Example", field:"example", topCalc:"unique"}
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
{title:"Age", field:"age", topCalc:adultCalc},
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
});
Calculations for Data Trees
When you are using the dataTree option with your table, the column calculations will by default only use the data for the top level rows and will ignore any children.
To include child rows in the column calculations set the dataTreeChildColumnCalcs option to true in the table constructor
var table = new Tabulator("#example-table", {
dataTree:true, //show data in a tree structure
dataTreeChildColumnCalcs:true, //include child rows in column calculations
});
Visible Children Column calculations will only include the child rows if their tree branch is open, hidden rows on closed branches are not included in calculations.
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
}
}
}