I want a google chart with combined x-axis values

Question

I am getting charts like this-->https://i.sstatic.net/SF0s0.jpg but I want google charts with combine x-axis like this--> https://i.sstatic.net/MXKKK.jpg

Here is my code:

google.charts.load('current', {'packages':['bar']});
google.charts.setOnLoadCallback(drawChart);

function drawChart() {
  var data = google.visualization.arrayToDataTable([
               ["BusinessUnit", "Year", "AvgEmpCost"],
               ["BU-1", 2014, 119329],
               ["BU-1", 2015, 125542],
               ["BU-1", 2016, 120894],
               ["BU-1", 2017, 130776],
               ["BU-1", 2018, 141465],
               ["BU-1", 2019, 153028],
               ["BU-2", 2014, 130722],
               ["BU-2", 2015, 131218],
               ["BU-2", 2016, 131451],
               ["BU-2", 2017, 134897],
               ["BU-2", 2018, 138432],
               ["BU-2", 2019, 142061],
  ]);

  var options = {
    chart: {
      title: 'buspentamt',

    }
  };

  var chart = new google.charts.Bar(document.getElementById('columnchart_material'));

  chart.draw(data, google.charts.Bar.convertOptions(options));
}

Answer 1

to produce the chart in question, you will need series columns for each year,
the data table will need a format similar to the following...

var data = google.visualization.arrayToDataTable([
  ["BusinessUnit", "2014", "2015", "2016"],
  ["BU-1", 119329, 125542, 120894],
  ["BU-2", 130722, 131218, 131451],
]);

not sure how the data is being built,
but we can use a data view to transform the row to columns,
then use the group function to aggregate the data.
this will prevent from having to modify the back-end query.

see following working snippet for an example...

  google.charts.load('current', {packages:['bar']});
  google.charts.setOnLoadCallback(drawChart);

  function drawChart() {
    var data = google.visualization.arrayToDataTable([
      ["BusinessUnit", "Year", "AvgEmpCost"],
      ["BU-1", 2014, 119329],
      ["BU-1", 2015, 125542],
      ["BU-1", 2016, 120894],
      ["BU-1", 2017, 130776],
      ["BU-1", 2018, 141465],
      ["BU-1", 2019, 153028],
      ["BU-2", 2014, 130722],
      ["BU-2", 2015, 131218],
      ["BU-2", 2016, 131451],
      ["BU-2", 2017, 134897],
      ["BU-2", 2018, 138432],
      ["BU-2", 2019, 142061],
    ]);

    var options = {
      chart: {
        title: 'buspentamt'
      },
      height: 400
    };

    // init column arrays
    var aggColumns = [];
    var viewColumns = [0];

    // build view & agg columns for each year
    data.getDistinctValues(1).forEach(function (year, index) {
      // add view column for year
      viewColumns.push({
        calc: function (dt, row) {
          if (dt.getValue(row, 1) === year) {
            return dt.getValue(row, 2);
          }
          return null;
        },
        label: year,
        type: 'number'
      });

      // add agg column
      aggColumns.push({
        aggregation: google.visualization.data.sum,
        column: index + 1,
        label: year,
        type: 'number'
      });
    });

    // set view columns
    var view = new google.visualization.DataView(data);
    view.setColumns(viewColumns);

    // agg view by business unit
    var aggData = google.visualization.data.group(
      view,
      [0],
      aggColumns
    );

    var chart = new google.charts.Bar(document.getElementById('columnchart_material'));

    // use aggData to draw chart
    chart.draw(aggData, google.charts.Bar.convertOptions(options));
  }

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="columnchart_material"></div>

EDIT

to count the number of years per BU,
see following working snippet...

google.charts.load('current', {packages:['bar']});
google.charts.setOnLoadCallback(drawChart);

function drawChart() {
  var data = google.visualization.arrayToDataTable([
    ["BusinessUnit", "Year"],
    ["BU-1", 2014],
    ["BU-1", 2015],
    ["BU-1", 2016],
    ["BU-1", 2017],
    ["BU-1", 2018],
    ["BU-1", 2019],
    ["BU-2", 2014],
    ["BU-2", 2015],
    ["BU-2", 2016],
    ["BU-2", 2017],
    ["BU-2", 2018],
    ["BU-2", 2019],
  ]);

  var options = {
    chart: {
      title: 'buspentamt'
    },
    height: 400
  };

  // init column arrays
  var aggColumns = [];
  var viewColumns = [{
    calc: function () {
      return 'Number of Years';
    },
    label: 'Years',
    type: 'string'
  }];

  // build view & agg columns for each bu
  data.getDistinctValues(0).forEach(function (bu, index) {
    // add view column for bu
    viewColumns.push({
      calc: function (dt, row) {
        if (dt.getValue(row, 0) === bu) {
          return 1;
        }
        return null;
      },
      label: bu,
      type: 'number'
    });

    // add agg column
    aggColumns.push({
      aggregation: google.visualization.data.sum,
      column: index + 1,
      label: bu,
      type: 'number'
    });
  });

  // set view columns
  var view = new google.visualization.DataView(data);
  view.setColumns(viewColumns);

  // agg view by business unit
  var aggData = google.visualization.data.group(
    view,
    [0],
    aggColumns
  );

  var chart = new google.charts.Bar(document.getElementById('chart'));

  // use aggData to draw chart
  chart.draw(aggData, google.charts.Bar.convertOptions(options));
}

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart"></div>