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 with no paramaters, which gets called once per cell in the pivot table and returns an object with the following keys:

• push: a function which takes a row from the input, this function is called once per row 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

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

var count = function() {
  return {
    count: 0,
    push: function(row) { 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 field-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 field for rows which match the cell. Note that the return value is very similar to the function above, save that it 'closes over' the variable field:

var countUnique = function(fieldArray) {
  var field = fieldArray[0];
  return function() {
    return {
      uniq: [],
      push: function(row) {
        var _ref;
        if (_ref = row[field], __indexOf.call(this.uniq, _ref) < 0) {
          this.uniq.push(row[field]);
        }
      },
      value: function() { return this.uniq.length; },
      format: function(x) { return x; },
      label: "Count Unique " + field
    };
  };
}

##Writing your own aggregators

If you are using PivotTable.js in a context where you already know the field-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 fields 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 field and a successes field 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(row) {
        if (!isNaN(parseFloat(row.successes))) {
          this.sumSuccesses += parseFloat(row.successes);
        }
        if (!isNaN(parseFloat(row[denom]))) {
          return this.sumTrials += parseFloat(row.trials);
        }
      },
      value: function() { return this.sumSuccesses / this.sumTrials; },
      format: function(x) { return x; },
      label: "Success Rate"
    };
  };
};

As it happens, calculating this sort of rate is pretty common, so PivotTable.js already includes a sumOverSum aggregator that you can wrap for this purpose, like this:

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

Note that if you wanted to use the successRate defined above as a aggregator parameter to pivot(), you will need to use only the inner function, which returns the object, rather than the generator function thereof.