Dashboard using google charts with two tables

I'm trying to create a Dashboard with column chart and a few category filters in my app, I have data like this :

['First name', 'City', 'Number of children'],
['Michael' , 'London', 2],
['Elisa', 'Paris', 3],
['Robert', 'Moskov', 3],
['John','LA', 1],
['Jessica', 'Kyiv', 3],
['Aaron', 'New York', 2],
['Margareth','Tokyo', 3 ]

but I have to visualize not this data, but a table with total amount of people with same number of children:

['1 child', '2 children', '3 children'],

[1, 2 , 4]

So when I apply some filter to first table than data in second table must be re-calculated automatically. Can I somehow bind this tables and controls together ? Or I have to put some handlers for each filter and re-calculate data manually ?

Solution

I assume that given your data:

['First name', 'City', 'Number of children'],
['Michael' , 'London', 2],
['Elisa', 'Paris', 3],
['Robert', 'Moskov', 3],
['John','LA', 1],
['Jessica', 'Kyiv', 3],
['Aaron', 'New York', 2],
['Margareth','Tokyo', 3 ]

You want to group by your 2nd column (number of children) to get this result:

[1, 1],
[2, 2],
[3, 4]

You can do this easily using the group by aggregation feature for data tables.

Here is sample code:

function drawJoin() {
  var dt = google.visualization.arrayToDataTable([
    ['First name', 'City', 'Number of children'],
    ['Michael' , 'London', 2],
    ['Elisa', 'Paris', 3],
    ['Robert', 'Moskov', 3],
    ['John','LA', 1],
    ['Jessica', 'Kyiv', 3],
    ['Aaron', 'New York', 2],
    ['Margareth','Tokyo', 3 ]
  ]);

  // Group dt by column 2, and count number of entries for each.
  var grouped_dt = google.visualization.data.group(
      dt, [2],
      [{'column': 0, 'aggregation': google.visualization.data.count, 'type': 'number'}]);


  var table = new google.visualization.Table(document.getElementById('table'));
  table.draw(dt, null);

  var grouped_table = new google.visualization.Table(document.getElementById('grouped_table'));
  grouped_table.draw(grouped_dt, null);
}

Feel free to try it out on Google Playground (just copy-paste the above code in).

You can graph that as is, or you can transpose it using a javascript function to transcribe rows/columns in your datatable.

So you should filter using your controls on the original data table, and then create a grouping function, and draw the grouped table in your chart.

If you want the labels to read '1 child' instead of just the number 1, then you need to create a function using SetFormattedValue() since the output of the group would be a number. You could mix this with the transpose function above since you're already doing work on the data table.