Aggregators

pivot(): aggregator parameter

The aggregator parameter of the pivot() function defines what will end up in the cells of the pivot table. It needs to be a function which gets called once per cell in the pivot table and returns an object with the following keys:

• push: a function which takes a record from the input, this function is called once per record which matches the cell
• value: a function which returns the value to be stored in the cell
• format: a function which takes a value and returns a formatted string representation
• numInputs: (defaults to 0) an integer which specifies how many input parameter dropdowns the UI generated by pivotUI() should show for this aggregator

Aggregator functions receive 3 parameters: a PivotData object, a rowKey and a colKey. The key parameters define where in the table this aggregator is located, and the reference to the PivotData object allows cell values to depend upon each other. Your can also use the rowKey and colKey parameters to determine if you are in a total cell ([] for rowKey means you are in a column total cell, [] for colKey means you are in a row total cell, and [] for both means you are in the grand total cell). See the Renderers documentation for a description of the PivotData object. These parameters are useful for accessing row/col/overall 'total' values for normalization. See the fractionOf aggregatorTemplate in the source for an example of how to do this.

Here is an example aggregator "count", which will just return the number of records which match the cell:

var count = function(data, rowKey, colKey) {
  return {
    count: 0,
    push: function(record) { this.count++; },
    value: function() { return this.count; },
    format: function(x) { return x; },
 };
};

pivotUI(): aggregators and aggregatorName parameters

The aggregators parameter of the pivotUI() function (note the s!), despite its similar name to the entity above, is actually a dictionary of functions which can used to generate an aggregator parameter for the pivot() function. The aggregator which will be used by default is the first one in the dictionary or the one denoted by the aggregatorName parameter. The values of the aggregators dictionary need to be functions which take as an argument an array of attribute-names (which the user can specify in the UI thanks to the numInputs parameter), and which returns a function consumable by pivot() as an aggregator parameter.

Here is an example of an aggregator-generating function "countUnique", which can generate an aggregator which will count the number of unique values of the given attribute for records which match the cell. Note that the return value is very similar to the function above, save that it 'closes over' the variable attribute:

var countUnique = function(attributeArray) {
  var attribute = attributeArray[0];
  return function(data, rowKey, colKey) {
    return {
      uniq: [],
      push: function(record) {
        var _ref;
        if (_ref = record[attribute], __indexOf.call(this.uniq, _ref) < 0) {
          this.uniq.push(record[attribute]);
        }
      },
      value: function() { return this.uniq.length; },
      format: function(x) { return x; },
      numInputs: 1
    };
  };
}

Writing your own aggregators

If you are using PivotTable.js in a context where you already know the attribute-names in the data, as well as the types of cell-values your users will want to see (i.e. you are writing a reporting system) then you can create your own aggregation functions so that users will not have to drag attributes into the aggregator-function box in the UI.

As an example, let's say you are using PivotTable.js to generate summary tables for data which has a trials attribute and a successes attribute and you know that your users will care a lot about the success rate, which is defined as the sum of successes over the sum of trials. You could therefore create an aggregator-generating function to pass into pivotUI() in the aggregators dictionary defined as follows:

var successRate = function() {
  return function() {
    return {
      sumSuccesses: 0,
      sumTrials: 0,
      push: function(record) {
        if (!isNaN(parseFloat(record.successes))) {
          this.sumSuccesses += parseFloat(record.successes);
        }
        if (!isNaN(parseFloat(record[denom]))) {
          return this.sumTrials += parseFloat(record.trials);
        }
      },
      value: function() { return this.sumSuccesses / this.sumTrials; },
      format: function(x) { return x; },
      numInputs: 0
    };
  };
};

Built-in Aggregators and Aggregator Templates

Some built-in aggregator generators are available under $.pivotUtilities.aggregators, and these are created by using less-specialized "aggregator templates" (i.e. aggregator generator generators) available under $.pivotUtilities.aggregatorTemplates.

For example, calculating the sort of rate above is pretty common, so PivotTable.js already includes a sumOverSum aggregator template that you can wrap for this purpose, like this:

var sumOverSum = $.pivotUtilities.aggregatorTemplates.sumOverSum;
var successRate = function() { return sumOverSum()(["successes", "trials"]); }

The careful reader will note an extra set of parentheses in the snippet above: sumOverSum()(["successes", "trials"]). This is where you can pass in extra parameters to the template, most usefully a custom output formatting function. If you are writing a 'language pack' for Localization purposes, this is where you can specify your thousands and decimal separators etc. Additionally, if you are creating an aggregator you know will be used for percentages, you can add % as a suffix, or if you know the aggregator operates on currencies, you can prefix a $ and put negative values in parentheses.

The built-in $.pivotUtilities.numberFormat helper can be used to create formatters. For example let's say I wanted a successRate aggregator with French-style number formatting. I could use:

var sumOverSum = $.pivotUtilities.aggregatorTemplates.sumOverSum;
var numberFormat = $.pivotUtilities.numberFormat;
var frFormat = numberFormat({thousandsSep:" ", decimalSep:","});
var successRate = function() { return sumOverSum(frFormat)(["successes", "trials"]); }

If you wanted an aggregator more like the built-in one where the user could specify which fields are "successes" and "trials", but still with French-style number formatting, then you would peel off one layer of function calls:

var sumOverSum = $.pivotUtilities.aggregatorTemplates.sumOverSum;
var numberFormat = $.pivotUtilities.numberFormat;
var frFormat = numberFormat({thousandsSep:" ", decimalSep:","});
var frenchSumOverSum = sumOverSum(frFormat);

Note that if you wanted to use define a successRate similar to above to use as an aggregator parameter to pivot() rather than pivotUI(), you would not need to wrap it in a extra function call, you would just pass sumOverSum()(["successes", "trials"]) as the aggregator parameter.