Aggregate over multiple fact fields. Result looks like this: http://imgur.com/fI636rq The aggregator returns first field for renderers without multifield support. The renderer is a modified version of the built-in table to support multifield. Quick and dirty but it gets the job done. For https://github.com/nicolaskruchten/pivottable.

multifact_sum_aggregator.js

multifactSumAggregator = function() {
  return function(facts) {

    return function() {
      var summedFacts = {};

      for (_i = 0, _len = facts.length; _i < _len; _i++) {
        summedFacts[facts[_i]] = 0
      }

      return {

        push: function(record) {
          for ( _i = 0, _len = facts.length; _i < _len; _i++) {
            summedFacts[facts[_i]] += parseFloat(record[facts[_i]]);
          }
        },

        multivalue: function() {
          return summedFacts;
        },

        // return the first element for unsupported renderers.
        value: function() { return summedFacts[facts[0]]; },
        format: function(x) { return x; },

        label: "Facts"
      };
    };
  };
}	

multifact_table_renderer.js

multifactTableRenderer = function(){
  return function(pivotData) {
    var aggregator, c, colAttrs, colKey, colKeys, i, j, r, result, rowAttrs, rowKey, rowKeys, th, totalAggregator, tr, txt, val, x;
    colAttrs = pivotData.colAttrs;
    rowAttrs = pivotData.rowAttrs;
    rowKeys = pivotData.getRowKeys();
    colKeys = pivotData.getColKeys();

    result = $("<table class='table table-bordered pvtTable'>");

    for (j in colAttrs) {
      c = colAttrs[j];
      tr = $("<tr>");
      if (parseInt(j) === 0 && rowAttrs.length !== 0) {

        tr.append($("<th>").attr("colspan", rowAttrs.length).attr("rowspan", colAttrs.length));
      }

      tr.append($("<th class='pvtAxisLabel'>").text(c));


      tmpAggregator = pivotData.getAggregator([], []);
      if (tmpAggregator.multivalue) {
        col_colspan = Object.keys(tmpAggregator.multivalue()).length;
        col_rowspan = 1
      } else {
        col_colspan = 1
        col_rowspan = 2
      }

      for (i in colKeys) {
        colKey = colKeys[i];
        th = $("<th class='pvtColLabel'>").text(colKey[j]).attr("colspan", col_colspan);
        if (parseInt(j) === colAttrs.length - 1 && rowAttrs.length !== 0) {
          th.attr("rowspan", col_rowspan);
        }
        tr.append(th);
      }
      if (parseInt(j) === 0) {
        tr.append($("<th class='pvtTotalLabel'>").text("Totals").attr("colspan", col_colspan).attr("rowspan", col_rowspan));
      }
      result.append(tr);
    }
    if (rowAttrs.length !== 0) {
      tr = $("<tr>");
      for (i in rowAttrs) {
        r = rowAttrs[i];
        tr.append($("<th class='pvtAxisLabel'>").text(r));
      }

      tmpAggregator = pivotData.getAggregator([], []);
      if (tmpAggregator.multivalue) {
        if (colAttrs.length > 0) {
          th = $("<th>");
          tr.append(th);
        }

        val = tmpAggregator.multivalue();
        for (i in colKeys) {
          for (v in val) {
            tr.append($("<th class='pvtColLabel'>").text(v).data("value", v));
          }
        }

        for (v in val) {
          tr.append($("<th class='pvtColLabel'>").text(v).data("value", v));
        }
      } else {
        th = $("<th>");
        if (colAttrs.length === 0) {
          th.addClass("pvtTotalLabel").text("Totals");
        }
        tr.append(th);
      }

      result.append(tr);
    }
    for (i in rowKeys) {
      rowKey = rowKeys[i];
      tr = $("<tr>");
      for (j in rowKey) {
        txt = rowKey[j];
        th = $("<th class='pvtRowLabel'>").text(txt).attr("rowspan", x);
        if (parseInt(j) === rowAttrs.length - 1 && colAttrs.length !== 0) {
          th.attr("colspan", 2);
        }
        tr.append(th);
      }
      for (j in colKeys) {
        colKey = colKeys[j];
        aggregator = pivotData.getAggregator(rowKey, colKey);

        if (aggregator.multivalue) {
          val = aggregator.multivalue();
          for (v in val) {
            tr.append($("<td class='pvtVal row" + i + " col" + j + "'>").text(aggregator.format(val[v])).data("value", val[v]));
          }
        } else {
          val = aggregator.value();
          tr.append($("<td class='pvtVal row" + i + " col" + j + "'>").text(aggregator.format(val)).data("value", val));
        }

      }
      totalAggregator = pivotData.getAggregator(rowKey, []);


      if (totalAggregator.multivalue) {
        val = totalAggregator.multivalue();
        for (v in val) {
          tr.append($("<td class='pvtTotal rowTotal'>").text(totalAggregator.format(val[v])).data("value", val[v]).data("for", "row" + i));
        }
      } else {
        val = totalAggregator.value();
        tr.append($("<td class='pvtTotal rowTotal'>").text(totalAggregator.format(val)).data("value", val).data("for", "row" + i));
      }

      result.append(tr);
    }
    tr = $("<tr>");
    th = $("<th class='pvtTotalLabel'>").text("Totals");
    th.attr("colspan", rowAttrs.length + (colAttrs.length === 0 ? 0 : 1));
    tr.append(th);
    for (j in colKeys) {
      colKey = colKeys[j];
      totalAggregator = pivotData.getAggregator([], colKey);

      if (totalAggregator.multivalue) {
        val = totalAggregator.multivalue();
        for (v in val) {
          tr.append($("<td class='pvtTotal colTotal'>").text(totalAggregator.format(val[v])).data("value", val[v]).data("for", "col" + j));
        }
      } else {
        val = totalAggregator.value();
        tr.append($("<td class='pvtTotal colTotal'>").text(totalAggregator.format(val)).data("value", val).data("for", "col" + j));
      }
    }

    totalAggregator = pivotData.getAggregator([], []);

    if (totalAggregator.multivalue) {
      val = totalAggregator.multivalue();
      for (v in val) {
        tr.append($("<td class='pvtGrandTotal'>").text(totalAggregator.format(val[v])).data("value", val[v]));
      }
    } else {
      val = totalAggregator.value();
      tr.append($("<td class='pvtGrandTotal'>").text(totalAggregator.format(val)).data("value", val));
    }

    result.append(tr);
    result.data("dimensions", [rowKeys.length, colKeys.length]);
    return result;
  };
};

report.js

// Putting the pieces together.

var custom_renderers = {
	"Multifact Table": multifactTableRenderer()
};

var custom_aggregators = {
	"Multifact aggregator": multifactSumAggregator()
};


$("#declarations_report #pivottable").pivotUI(data, {
	aggregators: $.extend(custom_aggregators, $.pivotUtilities.aggregators),
	renderers: $.extend(custom_renderers, $.pivotUtilities.renderers, $.pivotUtilities.gchart_renderers),
	rows: ["person"],
	cols: ["company"],
	vals: ["hours", "turnover", "profit_margin"],
	hiddenAttributes: ["id"]
});

can you give data example?? it's not working with my data

hi
great work stephanvd
who can i use it with pivot not pivotUI

Awesome work stephanvd, thank you very much. I have a question about this project. There you choose which fields you wanted for the "facts" (the VALS array). Did you try to write something so the user can choose them, like the "SUM" aggregator ?

I don't know how dificult it can be, because when I was experimenting with the numInputs I couldn't get a simple aggregator to work :( so I was wondering how difficult this could be, or if you can point me on how to use the numInputs, I could try adapting this plugin of yours.

Edit: Nevermind, pulled it off. For this example you need only to add "numInputs: n", n being the number of inputs you want.

Cheers

Hi raaffaaeell,

Would you mind sharing with me your entire update to the solution from stephanvd?

I tried setting "numInputs: 3" in the pivotUI setup but would not get the option do do dedicated aggregation for the different dolumn/datavalues.

Many thanks

thanks stephanvd, this is exactly what i was looking for, a multi-aggregator.
maybe nicolas can get it added to the main library...

@stephanvd

thnQ, i used your three files , modified to get Sub-totals(for first row element) of each val element and added some custom deriver to get sum of two columns results third column finally injected them into pivot.js files.......... now its working fine with even other recderes and aggrgators :)

Hello.
I make changes in @unk1911 version (remove problem with col length and colspan in header) and make this fiddle:
https://jsfiddle.net/dL86h8xr/

Cheers

@ zuk38 - your jsfiddle is great - however a there is a bug when adding more then 2 columns. The table becomes out of alignment

Hi guy's I am new to R.
I need to use Pivot functionality in R so using "rpivotTable" library which alows user to select only one field for aggregation(i.e vals=" ", only field).
Is there any Library where I can have multiple (atleast 2-4 fields) in an aggregation.

Thanks in advance.. :)

@zuk38

hi, did you fix the bug when adding more than 2 columns as 2bitcoder says. Thank you.

@zuk38

Hi, it is working good but sum values are coming empty. how can i get those value. will you please help me.

Hi Stephen,

i am using outsystems with the base Pivot. can you please help me how to use this script in outsytems and generate the report.. Any help would be grateful.

Regards
Ryoto

Trying to get this to work in a very nonGeneric use case.

It'd appear that some of the changes to the core break this

I get
TypeError: this.allTotal.push is not a function

@dmclark
I go the same TypeError. Here are the updates I made to multifact_sum_aggregator.js to make it work with the latest core.

var multifactSumAggregator = function (facts) {
    return function () {
 
            var summedFacts = {};
            for (_i = 0, _len = facts.length; _i < _len; _i++) {
                summedFacts[facts[_i]] = 0
            }

            return {
                push: function (record) {
                    for (_i = 0, _len = facts.length; _i < _len; _i++) {
                        summedFacts[facts[_i]] += parseFloat(record[facts[_i]]);
                    }
                },
                value: function () {
                    return summedFacts[facts[0]];
                },
                multivalue: function () {
                    return summedFacts;
                },
                format: function (x) {
                    return x;
                },
                numInputs: 2
            };
   
    };
}

Hi, thank you, looks like this is a good work, but it doesn't work for me.
I tried to use both (old and new) code that you provided.
Can you please check what's wrong?
Here's a link to the page http://ru.sh.emet-solutions.com/pivot/a.html
Thank you!

I workarouned the problem with adding facts = ["true_total", "false_total", "grand_total"]; to multifactSumAggregator function but anyway it's not clear why UI doesn't have these 3 fields so that when i try to change campaign and redraw the table it doesn't have value array at all...

To fix the problem with colspan, you just need to change:

          val = aggregator.value();
          tr.append($("<td class='pvtVal row" + i + " col" + j + "'>").text(aggregator.format(val)).data("value", val));

to

val = aggregator.value();
          if (val) {
            tr.append($("<td class='pvtVal row" + i + " col" + j + "'>").text(aggregator.format(val)).data("value", val));
          }
          else {
            tmpAggregator = pivotData.getAggregator([], []);
            var cols_length = 1;
            if (tmpAggregator.multivalue) {
              cols_length = Object.keys(tmpAggregator.multivalue()).length;
            }
            for (var cl = 0; cl < cols_length; cl++) {
              tr.append($("<td class='pvtVal row" + i + " col" + j + "-" + cl + "'>").text("").data("value", null));
            }
          }

on line 99 of multifact_table_renderer.js

Is it right @zuk38 ?

Anybody knows how to implement a chart renderer for this multi sum?

I just wanted to share my refactoring of this to allow it to work more generally with the latest version of the pivot.js library.

      function buildMultiAggregator(names) {
            var numInputs = 0;
            var aggregators = $.map(names, function(a) { 
                var agg = $.pivotUtilities.aggregators[a];
                var ni = agg([])().numInputs || 0;
                numInputs += ni;
                return {
                    aggregator: agg,
                    numInputs: ni,
                    name: a
                }
            });
            return function multiAggregator(facts) {
                var offset = 0;
                var factsAggs = $.map(aggregators, function(a) {
                    var next = offset + a.numInputs;
                    var f = facts.slice(offset, next);
                    var fa = a.aggregator(f);
                    offset = next;
                    return {
                        aggregator: fa,
                        name: a.name + ''
                    };
                });
                return function (data, rowKey, colKey) {                
                    var aggs = $.map(factsAggs, function(a, i) {
                        var agg = a.aggregator(data, rowKey, colKey);
                        if(agg.inner) {
                            //This is detection that we have a fractionOf aggregator that needs to be "fixed"
                            agg.value = function() {
                                var parent = data.getAggregator.apply(data, this.selector);
                                if(parent.multivalue)
                                    parent = parent.multivalue()[i].aggregator;
                                return this.inner.value() / parent.inner.value();
                            }
                        }
                        return {
                            aggregator: agg,
                            name: a.name
                        };
                    });
                    return {
                        push: function (record) {
                            for(var i = 0 ; i < aggs.length; i++) {
                                aggs[i].aggregator.push(record);
                            }
                        },
                        multivalue: function () {
                            return aggs;
                        },
                        value: function (i) {
                            i = i || 0;
                            return aggs[i].aggregator.value();
                        },
                        format: function (x, i) {
                            i = i || 0;
                            return aggs[i].aggregator.format(x);
                        },
                        numInputs: numInputs
                    };
                };
            };
        }

And the renderer is defined as

    function multiTableRenderer(pivotData, opts) {
        opts = opts || {
            localeStrings: {
                totals: 'Totals'
            }
        };        
        var colAttrs = pivotData.colAttrs;
        var rowAttrs = pivotData.rowAttrs;
        var rowKeys = pivotData.getRowKeys();
        var colKeys = pivotData.getColKeys();
        var tmpAggregator = pivotData.getAggregator([], []);
        var multivalue = tmpAggregator.multivalue && tmpAggregator.multivalue();
        var aggcount = (multivalue && multivalue.length) || 1;
        var rowRenderers = $.map(rowAttrs, function(column, index) {
            return getRenderer({
                length: rowKeys.length,
                getValue: function(i) {
                    return rowKeys[i][index];
                }
            }, column);
        });
        var colRenderers = $.map(colAttrs, function(column, index) {
            return getRenderer({
                length: colKeys.length,
                getValue: function(i) {
                    return colKeys[i][index];
                }
            }, column);
        });

        function addDataCells(tr, i, j) {
            var colKey = j === -1 ? [] : colKeys[j];
            var rowKey = i === -1 ? [] : rowKeys[i];
            var aggregator = pivotData.getAggregator(rowKey, colKey);

            var rcClass, v, x;
            if(i === -1 && j === -1) {
                rcClass = 'pvtGrandTotal';
            } else if(i === -1) {
                rcClass = 'pvtTotal colTotal';
            } else if (j === -1) {
                rcClass = 'pvtTotal rowTotal';
            } else {
                rcClass = 'pvtVal row' + i + ' col' + j;
            }
            if (aggregator.multivalue) {
                var mv = aggregator.multivalue();
                for (x = 0; x < mv.length; x++) {
                    v = mv[x].aggregator.value();
                    $("<td class='" + rcClass + "'>")
                        .text(aggregator.format(v, x))
                        .data("value", v)
                        .appendTo(tr);
                }
            } else if(aggcount > 1) {
                //This handles data cells where there is no data at all. just make sure to fill it in with blanks
                for (x = 0; x < aggcount; x++) {
                    $("<td class='" + rcClass + "'>")
                        .data("value", null)
                        .appendTo(tr);
                }
            } else {
                var val = aggregator.value();
                $("<td class='" + rcClass + "'>")
                    .text(aggregator.format(val))
                    .data("value", val)
                    .appendTo(tr);
            }
        }
        
        var c, colKey, i, j, r, result, rowKey, th, tr, txt, x, v;

        result = $("<table class='pvtTable'>");

        var colspans = [];
        for (i = 0; i < colAttrs.length; i++)
            colspans.push(0);
        
        for (j = 0; j < colAttrs.length; j++) {
            c = colAttrs[j];
            tr = $("<tr>");
            if (j === 0 && rowAttrs.length !== 0) {
                tr.append($("<th>").attr("colspan", rowAttrs.length).attr("rowspan", colAttrs.length));
            }

            tr.append($("<th class='pvtAxisLabel'>").text(c));

            for (i= 0; i < colKeys.length; i++) {
                colKey = colKeys[i];
                txt = colKey[j];
                x = colspans[j];
                if(!x) {
                    for (x = 1; i + x < colKeys.length; x++) {
                        //This keeps smaller groupings from spanning past their parent
                        if(j > 0) {
                            //This logic is not correct yet. For the column headers we have to compare each
                            //parent to the next as we go
                            //isBreak = false;
                            //for(z = j -1; z >= 0; z--) {
                            //    if(colKeys[i + x])
                            //}
                            if(x === colspans[j-1] + 1)
                                break;
                        }
                        //If the next row doesnt match our rows value
                        if(colKeys[i + x][j] !== txt)
                            break;
                    }
                    th = $("<th class='pvtColLabel'>").text(colRenderers[j](txt === 'null' ? '' : txt));
                    if(x > 1 || aggcount > 1)
                        th.attr("colspan", x * aggcount);
                    tr.append(th);
                    if (j === colAttrs.length - 1 && rowAttrs.length !== 0)
                        th.attr("rowspan", aggcount === 1 ? 2 : 1);
                }
                colspans[j] = x-1;
            }
            if (j === 0) {
                th = $("<th class='pvtTotalLabel'>").text(opts.localeStrings.totals).attr("colspan", aggcount);
                th.attr("rowspan", colAttrs.length + (rowAttrs.length === 0 ? 0 : 1) - (aggcount === 1 ? 0 : 1));
                tr.append(th);
            }
            result.append(tr);
        }
        
        if (rowAttrs.length !== 0) {
            tr = $("<tr>");
            for (i = 0; i < rowAttrs.length; i++) {
                r = rowAttrs[i];
                tr.append($("<th class='pvtAxisLabel'>").text(r));
            }

            if (aggcount > 1) {
                if (colAttrs.length > 0) {
                    th = $("<th>");
                    tr.append(th);
                }

                for (i = 0; i < colKeys.length; i++) {
                    for (j = 0; j < multivalue.length; j++) {
                        v = multivalue[j];
                        tr.append($("<th class='pvtColLabel'>").text(v.name).data("value", v.name));
                    }
                }

                for (j = 0; j < multivalue.length; j++) {
                    v = multivalue[j];
                    tr.append($("<th class='pvtColLabel'>").text(v.name).data("value", v.name));
                }
            } else {
                th = $("<th>");
                if (colAttrs.length === 0) {
                    th.addClass("pvtTotalLabel").text(opts.localeStrings.totals);
                }
                tr.append(th);
            }

            result.append(tr);
        }
        
        var rowspans = [];
        for (i = 0; i < rowAttrs.length; i++)
            rowspans.push(0);
        
        //Adds all the data into the table and the column totals at the end of each row
        for (i = 0; i < rowKeys.length; i++) {
            rowKey = rowKeys[i];
            tr = $("<tr>");
            for (j = 0; j < rowKey.length; j++) {
                txt = rowKey[j];
                x = rowspans[j];
                if(!x) {                    
                    for (x = 1; i + x < rowKeys.length; x++) {
                        //This keeps smaller groupings from spanning past their parent
                        if(j > 0) {
                            if(x === rowspans[j-1] + 1)
                                break;
                        }
                        //If the next row doesnt match our rows value
                        if(rowKeys[i + x][j] !== txt)
                            break;
                    }
                    th = $("<th class='pvtRowLabel'>").text(rowRenderers[j](txt === 'null' ? '' : txt));
                    if(x > 1)
                        th.attr("rowspan", x);
                    tr.append(th);
                    if (j === rowAttrs.length - 1 && colAttrs.length !== 0)
                        th.attr("colspan", 2);
                }
                rowspans[j] = x-1;
            }
            for (j = 0; j < colKeys.length; j++) {
                addDataCells(tr, i, j);
            }
            addDataCells(tr, i, -1);
            result.append(tr);
        }
        //Add the bottom row total row
        tr = $("<tr>");
        th = $("<th class='pvtTotalLabel'>").text(opts.localeStrings.totals);
        th.attr("colspan", rowAttrs.length + (colAttrs.length === 0 ? 0 : 1));
        tr.append(th);
        for (j = 0; j < colKeys.length; j++) {
            addDataCells(tr, -1, j);
        }
        addDataCells(tr, -1, -1);
        result.append(tr);
        result.data('numrows', rowKeys.length);
        result.data('numcols', colKeys.length);
        return result;
    }

Then to use all the built in renderers you can do this

    var custom_renderers = {
        "Table": function(data, opts) {
            return multiTableRenderer(data, opts);
        },
        "Table Barchart": function(data, opts) {
            return multiTableRenderer(data, opts).barchart();
        },
        "Heatmap": function(data, opts) {
            return multiTableRenderer(data, opts).heatmap("heatmap", opts);
        },
        "Row Heatmap": function(data, opts) {
            return multiTableRenderer(data, opts).heatmap("rowheatmap", opts);
        },
        "Col Heatmap": function(data, opts) {
            return multiTableRenderer(data, opts).heatmap("colheatmap", opts);
        }
    };

Then to add the aggregator you would do something like the following

                var agg = buildMultiAggregator(['Sum', 'Count']);

                var pivotaggs = $.extend({
                    'Analytics': agg
                }, $.pivotUtilities.aggregators);

                $p.pivotUI(res, {
                    aggregators: pivotaggs,
                    renderers: custom_renderers,
                    aggregatorName: 'Analytics',
                    vals: ['SumColumn'],
                    rows: rows,
                    cols: cols
                });

@park896
Hi park896
Can you have any example?I can't get him to work.

Many thanks

Just to let others know trying to use this to render multiple values but the latest version by @park896 is incomplete. There are internal function(s) missing that are using in rendering that are not defined like getRenderer(). Working to see how to work around.

Hello, this solution looks great!
Since I am new to this, could you please tell me how to implement it in my current project based on pivottable.js?
Should I just add these files here and include them in mu index HTML or I should add the code in the pivottable.js?

Many thanks

To fix the problem with colspan, you just need to change:

          val = aggregator.value();
          tr.append($("<td class='pvtVal row" + i + " col" + j + "'>").text(aggregator.format(val)).data("value", val));

to

val = aggregator.value();
          if (val) {
            tr.append($("<td class='pvtVal row" + i + " col" + j + "'>").text(aggregator.format(val)).data("value", val));
          }
          else {
            tmpAggregator = pivotData.getAggregator([], []);
            var cols_length = 1;
            if (tmpAggregator.multivalue) {
              cols_length = Object.keys(tmpAggregator.multivalue()).length;
            }
            for (var cl = 0; cl < cols_length; cl++) {
              tr.append($("<td class='pvtVal row" + i + " col" + j + "-" + cl + "'>").text("").data("value", null));
            }
          }

on line 99 of multifact_table_renderer.js

Is it right @zuk38 ?

Thanks for this solution.