DC.JS Crossfilter - add running cumulative sum

1
votes

I have defined a table for a portfolio's profit and loss. For each single day the P&L is shown in appropriate DC bar chart, but I want to add a cumulative sum that follows the column date. This should in the example return for End of September only the P&L slice for September, for October the slice for both September and October and so one. If any filter applies in the crossfilter, the applied cumulative sum should apply on filtered data.

*

Date,Cpty,internalRating,ratingSegment,externalRating,Sector,assetClass,riskFactor,riskBucket,sensi,marketMove,PL
30.09.16,DEF,2A,IND,AA,Industrials,IR,EUR,10,-1000,2,-2000
30.09.16,JKL,3B,SERV,BBB,Services,IR,USD,10,-1000,4,-4000
30.09.16,JKL,3B,SERV,BBB,Services,IR,JPY,10,-10000,6,-60000
30.09.16,JKL,3B,SERV,BBB,Services,CS,CDS_JKL,10,-4000,4,-16000
30.09.16,MNO,2B,TRAN,BB,Transportations,IR,EUR,10,1000,-4,-4000
30.09.16,MNO,2B,TRAN,BB,Transportations,CS,CDS_MNO,10,-1000,5,-5000
31.10.16,DEF,2A,IND,AA,Industrials,IR,EUR,10,-1500,6,-9000
31.10.16,JKL,3B,SERV,BBB,Services,IR,USD,10,1500,12,18000
31.10.16,JKL,3B,SERV,BBB,Services,IR,JPY,10,15000,18,270000
31.10.16,JKL,3B,SERV,BBB,Services,CS,CDS_JKL,10,6000,12,72000
31.10.16,MNO,2B,TRAN,BB,Transportations,IR,EUR,10,-1500,-12,18000
31.10.16,MNO,2B,TRAN,BB,Transportations,CS,CDS_MNO,10,1500,15,22500
30.11.16,DEF,2A,IND,AA,Industrials,IR,EUR,10,1428,6,8568
30.11.16,JKL,3B,SERV,BBB,Services,IR,USD,10,1085,12,13020
30.11.16,JKL,3B,SERV,BBB,Services,IR,JPY,10,5046,18,90828
30.11.16,JKL,3B,SERV,BBB,Services,CS,CDS_JKL,10,2579,12,30948
30.11.16,MNO,2B,TRAN,BB,Transportations,IR,EUR,10,-253,-12,3036
30.11.16,MNO,2B,TRAN,BB,Transportations,CS,CDS_MNO,10,409,15,6135

*

Any clue how I can proceed ? Do I need reductio for this ? Thanks in advance !

2
javascriptdc.jscrossfilter

2 Answers

2
votes

As an alternative to @Ethan's clever answer, this is also a great opportunity for a "fake group" (and one of the first documented uses of that technique IIRC).

From the dc.js FAQ:

function accumulate_group(source_group) {
    return {
        all:function () {
            var cumulate = 0;
            return source_group.all().map(function(d) {
                cumulate += d.value;
                return {key:d.key, value:cumulate};
            });
        }
    };
}

Use it like this:

data.forEach(function(r) {
    r.Date = dateFormat.parse(r.Date);
});
var cf = crossfilter(data)
var dateDim = cf.dimension(function(d) { return d.Date; });
var plGroup = dateDim.sum(function(d) { return d.PL; });
var accumPLGroup = accumulate_group(plGroup);
2
votes

If you are using Crossfilter 1.4.0-alpha.06, you can do this using an array dimension. Something like the following:

function getDates(d) {
  // This function should return an array of dates or months from
  // d.Date until the end of the year.
  return [...]
}
var cf = crossfilter(data)
var runningSumDim = cf.dimension(getDates, true)
var runningSumGroup = runningSumDim.sum(function(d) { return d.PL; })

That should "just work" once you work out the logic to derive the array of subsequent months.

What does this do? The dimension accessor should return an array. This array is assumed to be the list of group values that this record should be included in. So you would want the array to include the current month key and the month keys of all subsequent months the record should be included in. Note, this should be an array of subsequent months, not previous months. Somewhat counter-intuitive, but think of it as the answer to the question "What months should this value be counted in?".