Search code examples
dc.jscrossfilterreductio

Using aggregates as a label in DC.JS

I'm creating a tool to do some data analysis on surgical material cost.

I would like to be able to aggregate total number of items per PO and divide it by number of levels per PO. Levels is flat and the same for each PO line, while total number is a result of aggregating Qty by PO.

I know how to do SOME aggregation, but I do not know how I would create a Row Chart (as an example) and show Implants per Level as a label, going from 1-10 or so down the line.

The main reason seems to be I want to Dimension off of PO, but that means number of unique POs becomes my label, and that is 140 in this data set.

I think I want to Dimension off of PO, divide Qty by Levels, create a unique QtyPerLevel object, and then dimension off of that.

I'm probably thinking about this wrong.

Example is here:

https://jsfiddle.net/7wbcref9/3/

var cpiGroup = reductio()
    .sum('Qty')
    .exception(function (d) { return d.PO; })
    .exceptionCount(true)(levels.group());
Solution

  • You would want to include both the sum and count on the same group so that you can add the count information into your 'fake' dimension that you are using for your table:

    var poGroup = reductio()
        .sum('total')
        .count(true)(poDimension.group());

var uniqueDim = {
    bottom: function (num) {
        var pos = poDimension.top(Infinity);
        // Uses top because the version of Crossfilter being used
        // doesn't support group.bottom.
        return poGroup.top(num)
          .filter(function (d) { return d.value.sum > 0; })
          .map(function (d) {
              var currPo = pos.filter(function (g) { return g.PO === d.key; })[0];
              return {
                  PO: d.key,
                  total: d.value.sum,
                  count: d.value.count,
                  Surgeon: currPo.Surgeon,
                  Levels: currPo.Levels,
                  date: currPo.date
              };
          });
    }
};

    Then in your table you could do what you are already doing for total for count as well:

    nasdaqTable /* dc.dataTable('.dc-data-table', 'chartGroup') */
    .dimension(uniqueDim)
    // Data table does not use crossfilter group but rather a closure
    // as a grouping function
    .group(function (d) {
        d.cpl = "$" + (d.total / d.Levels).toFixed(2);
        d.countperLevel = (d.count / d.Levels).toFixed(2);
        d.total = "$" + d.total.toFixed(2);
        return d.Surgeon;
    })
    // (_optional_) max number of records to be shown, `default = 25`
    .size(1000)
    // There are several ways to specify the columns; see the data-table documentation.
    // This code demonstrates generating the column header automatically based on the columns.
    .columns([
        'date', 'PO', 'Surgeon', 'Levels', 'total', 'count', { label: "Cost per Level", format: function (d) { return d.cpl }}, { label: "Count per Level", format: function(d) { return d.countperLevel; }}
    ])

    I'm not sure you really need to use group in your table to pre-calculate your values. It's probably better to do the calculation directly in the format function, but either way should work.

    Example: https://jsfiddle.net/optgf9d3/

    Also, with Reductio you can have arbitrary numbers of aggregations (including exception counts on different properties) on the same group by using the value syntax. You may find this handy as you find yourself needing to do different types of rate calculations all based on the same group: https://github.com/crossfilter/reductio#aggregations-standard-aggregations-reductio-b-value-b-i-propertyname-i-