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 Functions

Built In Functions

Tabulator comes with a number of preconfigured calculations including:

  • avg - the average value of the column
    • optional topCalcParams/bottomCalcParams:
      • 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
  • max - the maximum value in the column
    • optional topCalcParams/bottomCalcParams:
      • precision - the number of decimals to display, setting this value to false will display however many decimals are provided with the number (default is false)
  • min - the minimum value in the column
    • optional topCalcParams/bottomCalcParams:
      • precision - the number of decimals to display, setting this value to false will display however many decimals are provided with the number (default is false)
  • sum - a sum of all values in the column (only sums numerical columns)
    • optional topCalcParams/bottomCalcParams:
      • precision - the number of decimals to display, setting this value to false will display however many decimals are provided with the number (default is false)
  • concat - join all values into one string
  • count - a count of all non empty cells in the column

You can pass an optional additional parameter with the each calculation option, topCalcParams and bottomCalcParams that should contain an object with additional information for configuring the calculation function.

{title:"Rating", field:"rating", topCalc:customCalcFunction, topCalcParams:{age:18}}

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

Note: At the moment all calculations are formatted as plain text, formatters for column calculations will be coming soon.

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
});

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
        }
    }
}