Skip to content

Instantly share code, notes, and snippets.

@stephanvd

stephanvd/multifact_sum_aggregator.js

Created October 31, 2013 09:33
Show Gist options
  • Save stephanvd/7246890 to your computer and use it in GitHub Desktop.
Save stephanvd/7246890 to your computer and use it in GitHub Desktop.
Download ZIP
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.
Raw
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"
};
};
};
}
Raw
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;
};
};
Raw
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"]
});
@DavyOnGit
Copy link

DavyOnGit commented Aug 18, 2018 •
edited
Loading

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

@yepstein-idt
Copy link

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!

@yepstein-idt
Copy link

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

@diegobill
Copy link

diegobill commented May 7, 2019 •
edited
Loading

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 ?

@diegobill
Copy link

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

@park896
Copy link

park896 commented Nov 7, 2019

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

@CoderWangGaoFeng
Copy link

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

Many thanks

@Austinb
Copy link

Austinb commented Aug 20, 2020

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.

@ilitty
Copy link

ilitty commented Jan 20, 2021

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

@diego-weber
Copy link

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment