Create a running sum graph in dc.js

Question

I am trying to create a running sum in crossfilter to use with dc.js. I have a set of records like the following :

records = [{"date":"2014-01-01","field1":"value1","field2":"value11","value_field":-20},
           {"date":"2014-01-02","field1":"value2","field2":"value12","value_field":100},
           {"date":"2014-01-03","field1":"value1","field2":"value11","value_field":-10},
           {"date":"2014-01-04","field1":"value2","field2":"value12","value_field":150},
           ]

So far I have created a barGraph which plays nicely with the other dimensions but I would like to be able to show an line graph of the theoretical field runnning_total (along the dimension date).

To have it done in crossfilter would allow me to then group using the fieldx fields and easily get the same running total graph but restricted to a subgroup of values using dc.js.

Any help is welcome.

Answer 1

Since you are grouping across date (as per your date dimension), the reduce() function would be used to perform aggregations grouped by date, as per the highlighted cells in my Mickey Mouse example below:

With a running total you'd need to perform an entirely different operation, looping down the rows:

You can aggregate the data and then append the running total field as follows. I've also included an example of how to calculate an average value, using the reduce function:

records = [{ "date": "2014-01-01", "field1": "value1", "field2": "value11", "value_field": -20 },
    { "date": "2014-01-02", "field1": "value2", "field2": "value12", "value_field": 100 },
    { "date": "2014-01-03", "field1": "value1", "field2": "value11", "value_field": -10 },
    { "date": "2014-01-04", "field1": "value2", "field2": "value12", "value_field": 150 }
];


var cf = crossfilter(records);                                 

var dimensionDate = cf.dimension(function (d) {
        return d.date;
    });


function reduceAdd(p, v) {
    p.total += v.value_field;
    p.count++;
    p.average = p.total / p.count;
    return p;
}
function reduceRemove(p, v) {
    p.total -= v.value_field;
    p.count--;
    p.average = p.count ? p.total / p.count : 0;
    return p;
}
function reduceInitial() {
    return {
        total: 0,
        count: 0,
        average: 0,
    };
}



var average = dimensionDate.group().reduce(reduceAdd, reduceRemove, reduceInitial).all();

var averageWithRunningTotal = appendRuningTotal(average);  

function appendRuningTotal(average) {
    var len = average.length,
        runningTotal = 0;
    for (var i = 0; i < len; i++) {
        runningTotal += average[i].value.total;
        average[i].RunningTotal = runningTotal;
    }

    return average;
}

And this returns the following:

{"key":"2014-01-01","value":{"total":-20,"count":1,"average":-20},"RunningTotal":-20}
{"key":"2014-01-02","value":{"total":100,"count":1,"average":100},"RunningTotal":80}
{"key":"2014-01-03","value":{"total":-10,"count":1,"average":-10},"RunningTotal":70}
{"key":"2014-01-04","value":{"total":150,"count":1,"average":150},"RunningTotal":220}

Answer 2

Well I know the op already built a solution but after struggling with for a while I was able to crack it, so posting it here if someone else searches for it.

using the cumulative for the following: https://groups.google.com/forum/#!topic/dc-js-user-group/W9AvkP_dZ0U

Running Sum:

var _group   = dim.group().reduceSum(function(d) {return 1;});

var group = {
all:function () {
 var cumulate = 0;
 var g = [];
 _group.all().forEach(function(d,i) {
   cumulate += d.value;
   g.push({key:d.key,value:cumulate})
 });
 return g;
}

};

for Trailing Twelve Month calculation:

var _group   = dateDim.group().reduceSum(function(d) {return d.revenue;});
var group = {
all:function () {
var g = [];
_group.all().forEach(function(d,i) {
    var cumulate = 0;
    var monthcount =0;
    var dt =new Date( d.key);
    var ct =  new Date(d.key);
    ct.setFullYear(ct.getUTCFullYear() -1);
    _group.all().forEach(function(d2,i) {
        var dt2 = new Date(d2.key);
        if(dt2 <= dt && dt2 > ct){
            cumulate += d2.value;
            monthcount++; 
        }
    })
    if(monthcount>=11){
        console.log( ' Dt ' + dt + ' ' + cumulate + ' months ' + monthcount); 
        g.push({key:d.key,value:cumulate})
        }
    });
                return g;
            }
        };