dc.js / crossfilter dimensions (year / week)

Question

I can't make a correct graph showing the data group by week (and different years).

pseudo SQL would give:

select sum ('task')
group by 'year', 'week' (and maybe later 'site')

My data looks more or less like this:

{ "date" : "20150427", "week" : 18, "site" : "a" "task" : 3, } 
{ "date" : "20150427", "week" : 18, "site" : "b" "task" : 4, } 
{ "date" : "20150427", "week" : 18, "site" : "c" "task" : 2, } 
{ "date" : "20150427", "week" : 18, "site" : "d" "task" : 3, } 
{ "date" : "20150428", "week" : 18, "site" : "a" "task" : 3, } 
{ "date" : "20150428", "week" : 18, "site" : "b" "task" : 3, } 
{ "date" : "20150429", "week" : 18, "site" : "c" "task" : 2, } 
{ "date" : "20150429", "week" : 18, "site" : "d" "task" : 3, } 
{ "date" : "20140512", "week" : 20, "site" : "d" "task" : 6, } 
{ "date" : "20140512", "week" : 20, "site" : "a" "task" : 6, } 
{ "date" : "20140513", "week" : 20, "site" : "b" "task" : 4, } 
{ "date" : "20140513", "week" : 20, "site" : "c" "task" : 1, } 
{ "date" : "20140519", "week" : 21, "site" : "b" "task" : 2, } 
{ "date" : "20140519", "week" : 21, "site" : "c" "task" : 1, } 
{ "date" : "20140519", "week" : 21, "site" : "d" "task" : 3, } 
{ "date" : "20140519", "week" : 21, "site" : "a" "task" : 1, } 
{ "date" : "20140520", "week" : 21, "site" : "b" "task" : 2, } 
{ "date" : "20140520", "week" : 21, "site" : "c" "task" : 3, } 
{ "date" : "20140520", "week" : 21, "site" : "d" "task" : 1, } 
{ "date" : "20140520", "week" : 21, "site" : "a" "task" : 1, }

This is a sample of my data, it can have different years, sites or tasks

I tried this and it doesn't work, because the barChart cumulates the week of all years:

d3.json("/graph", function(dataJson) {


var dataTable = dc.dataTable("#dc-table-graph");
var barChart  = dc.barChart("#chart-bar-ordersperweek");

var data = dataJson;
var dateFormat = d3.time.format("%Y%m%d");
data.forEach(function (d) { d.date = dateFormat.parse(d.date); });


//dimension
var dateDim = ndx.dimension(function (d) { return d.date; });
var weekDim = ndx.dimension(function(d) {return d.week;});

//group
var orderByWD = weekDim.group().reduceSum(function (d) { return d.task; });


barChart
    .width(1000).height(250)
    .dimension(weekDim)
    .group(orderByWD)
    .x(d3.scale.ordinal().domain(d3.range(minWeek.week-1,maxWeek.week+1)))
    .xUnits(dc.units.ordinal)
    .y(d3.scale.linear().domain([0, 6000]))
    .margins({top: 20, right: 30, bottom: 50, left: 80})
    .label(function(d) { return d.value})
    ;


dataTable.width(800).height(800)
        .dimension(dateDim)     
        .group( function (d) { return 'Week ' + d.week} )
        .size(5000)
        .columns([
            function (d) { var format = d3.format('02d');
                return d.date.getFullYear() +'/'+ format(d.date.getMonth()+1) + '/' + format(d.date.getDate()); },
            function (d) { return d.week; },
            function (d) { return d.site; },
            function (d) { return d.task; }
        ])
        .sortBy( function (d) { return Number(d.date) + d.task; })
        .order(d3.descending);

        dataTable.on('renderlet', function(chart){
            chart.selectAll('.dc-table-group').classed('info',true);
        });

dc.renderAll();

My goal is to sum all task happened in same week and draw it to row chart and when I select a week that update the datatable with selected week.

In the dataTable how do I sum (cumulate) task by site grouped by week ?

Thanks for any help

EDIT i have add a jsfiddle https://jsfiddle.net/d4qsd8wh/

Answer 1

I'm not sure if I've got the question right, since the other answers seem to be solving different problems. I will assume you're just asking how to get group-by-week to work when the week entry on your fields does not specify the year - so you get weeks from different years piled into one.

In other words I interpret this as the heart of your question:

I tried this and it doesn't work, because the barChart cumulates the week of all years

Which isn't really about the dataTable but the barChart.

To answer that question: yes, I think your week field is useless. Instead, use the parsed dates and d3.time.week to bin by week:

var weekDim = ndx.dimension(function(d) {return d3.time.week(d.date);});

Then set your bar chart to use date units instead of ordinals:

barChart
    ...
    .x(d3.time.scale())
    .xUnits(d3.time.weeks)
    .elasticX(true) // or calculate minWeek/maxWeek using date objects

What d3.time.week does, like the other d3 time interval functions, is return the date which is at the beginning of the interval. (Since d3.time.week is a synonym for d3.time.sunday, it returns midnight on the Sunday before each date - use d3.time.monday if you want Monday-based weeks instead.) Clipping each date to the beginning of its interval is a good way to bin the dates without forgetting the year they are a part of.

Then d3.time.weeks (notice the s) is a related function which can count the number of intervals (weeks) within any time range. dc.js needs an xUnits counting function in order to determine the number of bars to show.

Given that you've gotten answers to three different questions, you might want to spend a minute refining your next question to make sure it's clear.

Answer 2

You need to create your weekDim as follows:

var weekDim = ndx.dimension(function(d) {
    return d.date.substring(0,4) + "" + d.week;
});

and then create group on this dimension as follows:

function reduceAdd(p, v) {
    return p + v.task;
}

function reduceRemove(p, v) {
    return p - v.task;
}

function reduceInitial() {
    return 0;
}
var m = weekDim.group().reduce(reduceAdd,reduceRemove,reduceInitial);

and you get the result as m.top(Infinity). I don't know how you can use this dataTable but the output of the above will be exactly what you want. Hope this helps

Answer 3

So, what you'll want to do is create a fake "dimension" for the dataTable. In your current approach you can do it like this:

// Actually a dimension keyed on week and site
var fakeDateDim = {
  top: function(d) {
    var m = dc.d3.map();
    dateDim.top(Infinity).forEach(function(g) {
      if(m.has(g.week + g.site)) {
        m.get(g.week + g.site).task += g.task
      } else {
        // Create the "record"
        m.set(g.week + g.site, {
            week: g.week,
          site: g.site,
          task: g.task
        })
      }
    })

    return m.values();
  }
}

Then in the dataTable, use that fakeDateDim in the place of your current dateDim. (You'll also have to remove the table columns for date and week.)

Here's a working example based on the example you put together: https://jsfiddle.net/s2c9rhxw/

Note that you can use d3.nest, as mentioned in the answer by @ian-h, but it masks some of what you are doing, so I think it's probably better to use lower-level constructs like Map or d3.map until you are comfortable with the calculation. Regardless of how you actually execute the calculation, the key is to wrap it in a fake dimension object that dc.js will be able to use.

Answer 4

D3 has some pretty powerful tools to help you shape your data. This is just an example. By making a key of the Year+Week we can sum the tasks

var out = d3.nest()
  .key(function(d) { return d.date.substring(0,4)+d.week; })
  .rollup(function(v) { return {
      date: v[0].date,
      task: d3.sum(v, function(d) { return d.task; })
     };
   })
  .entries(data);

That will make out an array of objects that looks like this.

[
  {
    "key": "201420",
    "values": {
      "date": "20140512",
      "task": 17
    }
  },
  {
    "key": "201421",
    "values": {
      "date": "20140519",
      "task": 14
    }
  },
  {
    "key": "201518",
    "values": {
      "date": "20150427",
      "task": 23
    }
  }
]

Since you are grouping by week then the date is mostly arbitrary so I just have it showing the first one in the list of grouped dates. You can also add another .key(function(d) { return d.site; } under the first .key to sub group by site.