Google Charts: custom view of columns in a table

Question

I am trying to create a Google Charts table with two dropdown buttons which show the main KPI as one column when selecting one of the dropdown buttons, as well as all KPI columns before that one.

For example, when the Main KPI 2 is selected and that column is shown, then also the KPI columns KPI 4, KPI 5, KPI 6 and KPI 7 should also be shown. And when you change the dropdown column to say Main KPI 3, then that column, as well as the columns KPI 9 and KPI 10, should be shown only.

Is this possible?

I have been trying to figure it out with the view in the Table options.
{type: 'number',label: data.getColumnLabel(2),calc: function (dt, row) {return dt.getValue(row, 3)}}

Here an example graphic:

Any ideas or suggestions?

Here the JSFiddle: https://jsfiddle.net/tototatutu/zgy4h75c/5/

Here the entire HTML File: https://drive.google.com/file/d/10MicBrGMsIvhTAvr5PevV-G9bNYtgPzt/view?usp=sharing

Answer 1

in order to show each individual kpi column,
the kpi must have its own column in the data table.

currently, all are in one column -- 'KPIs'

we can transform the existing data table using a data view.

here, we use data table method getDistinctValues(colIndex) to build a list of the unique KPIs,
then add a unique column to the data view for each
we also aggregate the data view, in order to have all the values on the same row.

// create data view with columns for each KPI
var view = new google.visualization.DataView(data);
var aggColumns = [];
var viewColumns = [0, 1];
data.getDistinctValues(2).forEach(function (kpi, index) {
  // add view column for kpi
  viewColumns.push({
    calc: function (dt, row) {
      if (dt.getValue(row, 2) === kpi) {
        return dt.getValue(row, 3);
      }
      return null;
    },
    label: kpi,
    type: 'number'
  });

  // add agg column for id
  aggColumns.push({
    aggregation: google.visualization.data.sum,
    column: index + 2,
    label: kpi,
    type: 'number'
  });
});
view.setColumns(viewColumns);

// aggregate data view
var aggData = google.visualization.data.group(
  view,
  [0, 1],
  aggColumns
);

next, in order to change the table view when the filter changes,
we will not be able to use the dashboard bind method.

instead, we draw the filters manually, using the original data table.
then listen for the 'statechange' event,
in order to build the table view, and draw.

// Create a filter, passing some options
var peergroupFilter = new google.visualization.ControlWrapper({
  controlType: 'CategoryFilter',
  containerId: 'filter_div',
  dataTable: data,
  options: {
    filterColumnLabel: 'Peer Group',
    ui: {
        labelStacking: 'vertical',
        allowTyping: false,
        allowMultiple: false,
        caption: 'Choose Peer Group',
        sortValues: false,
        allowNone: false,
    }}
});
google.visualization.events.addListener(peergroupFilter, 'statechange', setKPI);
peergroupFilter.draw();

// Create a filter, passing some options
var kpiFilter = new google.visualization.ControlWrapper({
  controlType: 'CategoryFilter',
  containerId: 'filter_div2',
  dataTable: data,
  options: {
    filterColumnLabel: 'KPIs',
    values: ['Main KPI 1', 'Main KPI 2', 'Main KPI 3', 'Main KPI 4', 'Main KPI 5', 'Main KPI 6', 'Main KPI 7'],
    ui: {
        labelStacking: 'vertical',
        allowTyping: false,
        allowMultiple: false,
        caption: 'Choose KPI',
        sortValues: false,
        allowNone: false,
    }}
});
google.visualization.events.addListener(kpiFilter, 'statechange', setKPI);
kpiFilter.draw();

when the 'statechange' event fires on either filter,
we get the selected filter values -- getState().

we use the peer group value to set the rows,
and the kpi filter to set the columns.
then draw the table.

function setKPI() {
  // get filter values
  var peerGroup = peergroupFilter.getState().selectedValues[0];
  var kpi = kpiFilter.getState().selectedValues[0];

  // get rows
  var rows = aggData.getFilteredRows([{
    column: 1,
    value: peerGroup
  }]);

  // get columns
  var cols = [0];
  switch (kpi) {
    case 'Main KPI 1':
      cols.push(getColumnIndex('KPI 1', aggData));
      cols.push(getColumnIndex('KPI 2', aggData));
      break;

    case 'Main KPI 2':
      cols.push(getColumnIndex('KPI 4', aggData));
      cols.push(getColumnIndex('KPI 5', aggData));
      cols.push(getColumnIndex('KPI 6', aggData));
      cols.push(getColumnIndex('KPI 7', aggData));
      break;

    case 'Main KPI 3':
      cols.push(getColumnIndex('KPI 9', aggData));
      cols.push(getColumnIndex('KPI 10', aggData));
      break;

    case 'Main KPI 4':
      cols.push(getColumnIndex('KPI 12', aggData));
      cols.push(getColumnIndex('KPI 13', aggData));
      break;

    case 'Main KPI 5':
      cols.push(getColumnIndex('KPI 15', aggData));
      cols.push(getColumnIndex('KPI 16', aggData));
      cols.push(getColumnIndex('KPI 17', aggData));
      break;

    case 'Main KPI 6':
      cols.push(getColumnIndex('KPI 19', aggData));
      cols.push(getColumnIndex('KPI 20', aggData));
      break;

    case 'Main KPI 7':
      cols.push(getColumnIndex('KPI 22', aggData));
      cols.push(getColumnIndex('KPI 23', aggData));
      break;
  }
  cols.push(getColumnIndex(kpi, aggData));

  // format columns
  cols.forEach(function (colIndex) {
    if (colIndex === 0) {
      return;
    }
    formatNumber.format(aggData, colIndex);
    formatBar.format(aggData, colIndex);
  });

  // set view, draw table
  Table.setView({
    columns: cols,
    rows: rows
  });
  Table.draw();
}

---

see following working jsfiddle...
https://jsfiddle.net/476kfovs/