Aggregators

aggregator parameter to pivot()

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
• label: a string which can be used as a label 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 (null for rowKey means you are in a column total cell, null for colKey means you are in a row total cell, and null 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; },
    label: "Count"
 };
};

aggregators parameter to pivotUI()

The aggregators parameter of the pivotUI() function, 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 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 by dragging them into the aggregator box with the dropdown, 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; },
      label: "Count Unique " + attribute
    };
  };
}

##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; },
      label: "Success Rate"
    };
  };
};

##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. Finally, the output of the output formatter can be HTML, so you can even customize the colour, size, etc of your output, or insert images or charts or whatever.

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.