Using aggregates as a label in DC.JS

Question

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

score 1 · Accepted Answer · answered May 09 '16 at 15:07

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-

Thanks for the response! I see what you are doing, but the aggregation isn't coming out quite right. PO 50047738 has a total count of 20 looking at the raw data. I'm not sure why it's adding up to 11 — Wesley, May 11 '16 at 20:10
I can see what is happening. The number 11 showing up is the total number of rows that PO 50047738 takes up in the database. The Count number we want to show is the total QTY from that PO's dimension, which should be 20. — Wesley, May 11 '16 at 22:07
Ah, ok. So you need another sum on qty? In that case use Reductio's value syntax and define an additional sum. — Ethan Jewett, May 11 '16 at 22:08
Can we do another sum on `Qty` and cast it as another variable? How would we then get this data and use it in a row chart instead of the table? — Wesley, May 11 '16 at 22:08
We are already doing the SUM on total. It looks like we access a sum via `value.sum`. Not sure how to do other sums — Wesley, May 11 '16 at 22:09
Take a look at the value syntax documentation (link at the end of the answer). Docs show how to define 2 sums on the same group. Get at the result using valueAccessor on the DC visualization. — Ethan Jewett, May 11 '16 at 22:11
Having trouble. I assume you are referencing `reducer.value("y").sum(function(d) { return d.num3; });` except in our case we would be using `d.Qty` https://jsfiddle.net/optgf9d3/1/ — Wesley, May 11 '16 at 22:26
It's a little screwy with how you have to keep track of the reducer when you use `value`. Here you go: https://jsfiddle.net/o3ngjqjo/1/ — Ethan Jewett, May 11 '16 at 22:38

Using aggregates as a label in DC.JS

1 Answers1