Google Charts: Creating summary charts after categ

I'm trying to figure out how to take an initial data table, apply multiple category filters to it, and illustrate a combination of simple and summary statistics of the resulting data subset through pie charts and bar graphs.

I've created a sample script out of the Google dependent-filters example that will help explain. In a nutshell, this dataset has two countries (USA and France) with two regions each (New York/California and Ile-de-France/Provence). The population of each region is broken down into four components: Dark Blue, Light Blue, Dark Green, and Light Green.

Here's what I want: when selections are made from the two category filters for country and region (e.g., USA & New York), I want (a) a bar chart that shows the populations of the four population groups in that region; (b) a pie chart that shows the two-way split between Darks and Lights in that region (using two slices, labeled "Dark" and "Light") and (c) a pie chart that shows the two-way split between Blues and Greens in that region (also using two slices, labeled "Blue" and "Green").

Getting (a) is simple enough, but I can't figure out the right approach for (b) and (c). I don't need to do more than one region at once (e.g., select USA and leave the region filter open, and get summary stats for New York and Cali combined). All I need is to take four rows of data from one region and produce three charts.

I've started experimenting with adding calculated columns in the dataview but then I realized that the number of rows needs to change, so simply appending an extra column isn't all that's needed. I need to take the four-row dataview given to me by the category filters and somehow transform it into two-row dataviews that sum up the four-row view, and do it in different ways for each pie chart. Problem is, I have no idea how to do this, and the Internet isn't helping.

Here's the patch of code I've been staring at for hours. I've lost some of my prior experimentation - I've been playing mostly with the pie chart "view:" sections, but after getting nowhere I reverted them to the same view as the bar chart and forgot to save a draft - so I'm not sure how helpful it's going to be. Nonetheless:

google.load('visualization', '1.1', {packages: ['controls']});

function drawVisualization() {
    // Prepare the data
var data = google.visualization.arrayToDataTable([
    ['Country', 'Region/State', 'Color', 'Population'],
    ['USA', 'California', 'Dark Green', 700000],
    ['USA', 'California', 'Light Green', 776733],
    ['USA', 'California', 'Dark Blue', 3000000],
    ['USA', 'California', 'Light Blue', 3694820],
    ['USA', 'New York', 'Dark Green', 2000000],
    ['USA', 'New York', 'Light Green', 657592],
    ['USA', 'New York', 'Dark Blue', 8000000],
    ['USA', 'New York', 'Light Blue', 3175173],
    ['France', 'Ile-de-France', 'Dark Green', 2000000],
    ['France', 'Ile-de-France', 'Light Green', 1093031],
    ['France', 'Ile-de-France', 'Dark Blue', 100000],
    ['France', 'Ile-de-France', 'Light Blue', 51372],
    ['France', 'Provence', 'Dark Green', 800000],
    ['France', 'Provence', 'Light Green', 252395],
    ['France', 'Provence', 'Dark Blue', 300000],
    ['France', 'Provence', 'Light Blue', 73556] 
]);

// Define category pickers for 'Country', 'Region/State' and 'City'
var countryPicker = new google.visualization.ControlWrapper({
    'controlType': 'CategoryFilter',
    'containerId': 'control1',
    'options': {
        'filterColumnLabel': 'Country',
        'ui': {
            'labelStacking': 'vertical',
            'allowTyping': false,
            'allowMultiple': false,
            'allowNone': false
        }
    },
        'state': {
        selectedValues: ['USA']
    }
});

var regionPicker = new google.visualization.ControlWrapper({
    'controlType': 'CategoryFilter',
    'containerId': 'control2',
    'options': {
        'filterColumnLabel': 'Region/State',
        'ui': {
            'labelStacking': 'vertical',
            'allowTyping': false,
            'allowMultiple': false,
            'allowNone': false
        }
    },
        'state': {
        selectedValues: ['California']
    }
});

// Define a bar chart to show 'Population' data
var barChart = new google.visualization.ChartWrapper({
    'chartType': 'BarChart',
    'containerId': 'chart1',
'options': {
  'width': 300,
  'height': 300
    },
    'view': {
        'columns': [2, 3]
    }
});

var pieChartA = new google.visualization.ChartWrapper({
    'chartType': 'PieChart',
    'containerId': 'chart2',
'options': {
  'width': 300,
  'height': 300
},
    'view': {
        'columns': [2, 3]
    }
});

var pieChartB = new google.visualization.ChartWrapper({
    'chartType': 'PieChart',
    'containerId': 'chart3',                
'options': {
  'width': 300,
  'height': 300
},
    'view': {
        'columns': [2, 3]
    }
});

// Create the dashboard.
var dash = new google.visualization.Dashboard(document.getElementById('dashboard'));
dash.bind(countryPicker, regionPicker);
dash.bind(regionPicker, [barChart, pieChartA, pieChartB]);
dash.draw(data);
}

google.setOnLoadCallback(drawVisualization);

Here's the JSFiddle:

http://jsfiddle.net/james_twc/7nLZ8/

Unfortunately, while it's working fine in the Google Code Playground it's not working in JSFiddle. I'm using jQuery 1.8.3, "no wrap - in(body)", normalized CSS unchecked, and "http://www.google.com/jsapi?fake=.js" as an external resource, all to no avail. To get the code to propagate you'll either have to wave your magic wand over my JSFiddle or copy/paste the code to the Playground or another environment that's less finicky about Google APIs.

Also: I'm open to re-configuring the database structure if that's what it takes.

Any helpful commentary is appreciated. Thank you!

James

To make this work, you have to aggregate the filtered data and use the aggregated data to draw your PieCharts. The Dashboards do not support this sort of relationship, so you have to cheat. Bind the BarChart to your Dashboard, but do not bind the PieCharts. Then set up a "ready" event handler for the BarChart that gets the filtered data, aggregates it, and draws the PieCharts with the aggregated data. Here's an example:

// use a "ready" event handler on the BarChart to aggregate the data for the PieCharts
google.visualization.events.addListener(barChart, 'ready', function () {
    // get the filtered data used to draw the BarChart
    var dt = barChart.getDataTable();

    // group data by dark/light
    var darkLightGroup = google.visualization.data.group(dt, [{
        type: 'string',
        label: dt.getColumnLabel(2),
        column: 2,
        modifier: function (val) {
            return val.split(' ')[0];
        }
    }], [{
        type: 'number',
        label: dt.getColumnLabel(2),
        column: 3,
        aggregation: google.visualization.data.sum
    }]);
    pieChartA.setDataTable(darkLightGroup);
    pieChartA.draw();

    // group data by color
    var colorGroup = google.visualization.data.group(dt, [{
        type: 'string',
        label: dt.getColumnLabel(2),
        column: 2,
        modifier: function (val) {
            return val.split(' ')[1];
        }
    }], [{
        type: 'number',
        label: dt.getColumnLabel(2),
        column: 3,
        aggregation: google.visualization.data.sum
    }]);
    pieChartB.setDataTable(colorGroup);
    pieChartB.draw();
});

see it working here: http://jsfiddle.net/asgallant/7nLZ8/4/