Google charts - ComboChart - bars and candlesticks

2
votes

I try create ComboChart where are bars and series of waterfall(candlestick) chart. I dont know how to create data input for this. Candlestick needs format like:

['Mon', 20, 28, 38, 45],
['Tue', 31, 38, 55, 66],
['Wed', 50, 55, 77, 80],
['Thu', 77, 77, 66, 50],
['Fri', 68, 66, 22, 15]

and bars in my case: 

['name', 'value', { role: 'style' }],
['test1', 8, '#00f'], 
['test2', 10,'#000'],
['test3', 19,'#f00'],

Is it even possible to do it ? Join bars and candlesticks ?

1
chartsgoogle-visualizationbar-chartcandlestick-chart
can you share how your output will look like? - Sahil Dhir
Hi, ofc, there is example imgur.com/GSdp0Yx - Lukas

1 Answers

0
votes
  1. use the series option to control the chart type for each series
    each series is represented by a column, or a set of columns, in the data
    starting with the first column that is not the x-axis or a column role

example: the columns for the chart you describe might resemble the following... 

"cols":[
  // x-axis
  {"label":"Category","type":"string"},

  // bar series -- 0
  {"label":"Bar 0","type":"number"},

  // bar series -- 1
  {"label":"Bar 1","type":"number"},

  // waterfall series -- 2
  {"label":"WF 2 - Bottom 1","type":"number"},
  {"label":"WF 2 - Bottom 2","type":"number"},
  {"label":"WF 2 - Top 1","type":"number"},
  {"label":"WF 2 - Top 2","type":"number"},
  {"role":"style","type":"string","p":{"role":"style"}},

  // bar series -- 3
  {"label":"Bar 3","type":"number"}
],

the candlestick chart requires 4 columns, in the above example, a style column role is also used,
as such, all five columns are considered part of series number 2

  1. when building data for a combochart,
    use null for the series values,
    when they do not coincide with the same x-axis value

example data: 

"rows":[
  // bar (series 0 & 1) data
  {"c":[{"v":"YTD"},{"v":14807893.054},{"v":11684139.912},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null}]},
  {"c":[{"v":"Last Year"},{"v":16307893.054},{"v":20684139.912},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null}]},

  // waterfall (series 2) data
  {"c":[{"v":"Budget"},{"v":null},{"v":null},{"v":0},{"v":0},{"v":22707893.613},{"v":22707893.613},{"v":"#007fff"},{"v":null}]},
  {"c":[{"v":"Contract Labor"},{"v":null},{"v":null},{"v":22707893.613},{"v":22707893.613},{"v":22534350.429},{"v":22534350.429},{"v":"#1e8449"},{"v":null}]},
  {"c":[{"v":"Contract Non Labor"},{"v":null},{"v":null},{"v":22534350.429},{"v":22534350.429},{"v":22930956.493},{"v":22930956.493},{"v":"#922b21"},{"v":null}]},
  {"c":[{"v":"Matls & Equip"},{"v":null},{"v":null},{"v":22930956.493},{"v":22930956.493},{"v":22800059.612},{"v":22800059.612},{"v":"#1e8449"},{"v":null}]},
  {"c":[{"v":"Other"},{"v":null},{"v":null},{"v":22800059.612},{"v":22800059.612},{"v":21993391.103},{"v":21993391.103},{"v":"#1e8449"},{"v":null}]},
  {"c":[{"v":"Labor"},{"v":null},{"v":null},{"v":21993391.103},{"v":21993391.103},{"v":21546003.177999996},{"v":21546003.177999996},{"v":"#1e8449"},{"v":null}]},
  {"c":[{"v":"Travel"},{"v":null},{"v":null},{"v":21546003.177999996},{"v":21546003.177999996},{"v":21533258.930999994},{"v":21533258.930999994},{"v":"#1e8449"},{"v":null}]},
  {"c":[{"v":"Training"},{"v":null},{"v":null},{"v":21533258.930999994},{"v":21533258.930999994},{"v":21550964.529999994},{"v":21550964.529999994},{"v":"#922b21"},{"v":null}]},
  {"c":[{"v":"Actual"},{"v":null},{"v":null},{"v":0},{"v":0},{"v":21550964.52999999},{"v":21550964.52999999},{"v":"#007fff"},{"v":null}]},

  // bar (series 3) data
  {"c":[{"v":"FCST"},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null},{"v":16182561.25}]}
]
  1. following is a working example of a combo chart with bars and candlesticks 

google.charts.load('current', {
  callback: function () {
    drawChart();
    $(window).resize(drawChart);
  },
  packages:['corechart', 'table']
});

function drawChart() {
  var dataChart = new google.visualization.DataTable({
    "cols":[
      // x-axis
      {"label":"Category","type":"string"},

      // bar series -- 0
      {"label":"Bar 0","type":"number"},

      // bar series -- 1
      {"label":"Bar 1","type":"number"},

      // waterfall series -- 2
      {"label":"WF 2 - Bottom 1","type":"number"},
      {"label":"WF 2 - Bottom 2","type":"number"},
      {"label":"WF 2 - Top 1","type":"number"},
      {"label":"WF 2 - Top 2","type":"number"},
      {"role":"style","type":"string","p":{"role":"style"}},

      // bar series -- 3
      {"label":"Bar 3","type":"number"}
    ],
    "rows":[
      // bar (series 0 & 1) data
      {"c":[{"v":"YTD"},{"v":14807893.054},{"v":11684139.912},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null}]},
      {"c":[{"v":"Last Year"},{"v":16307893.054},{"v":20684139.912},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null}]},

      // waterfall (series 2) data
      {"c":[{"v":"Budget"},{"v":null},{"v":null},{"v":0},{"v":0},{"v":22707893.613},{"v":22707893.613},{"v":"#007fff"},{"v":null}]},
      {"c":[{"v":"Contract Labor"},{"v":null},{"v":null},{"v":22707893.613},{"v":22707893.613},{"v":22534350.429},{"v":22534350.429},{"v":"#1e8449"},{"v":null}]},
      {"c":[{"v":"Contract Non Labor"},{"v":null},{"v":null},{"v":22534350.429},{"v":22534350.429},{"v":22930956.493},{"v":22930956.493},{"v":"#922b21"},{"v":null}]},
      {"c":[{"v":"Matls & Equip"},{"v":null},{"v":null},{"v":22930956.493},{"v":22930956.493},{"v":22800059.612},{"v":22800059.612},{"v":"#1e8449"},{"v":null}]},
      {"c":[{"v":"Other"},{"v":null},{"v":null},{"v":22800059.612},{"v":22800059.612},{"v":21993391.103},{"v":21993391.103},{"v":"#1e8449"},{"v":null}]},
      {"c":[{"v":"Labor"},{"v":null},{"v":null},{"v":21993391.103},{"v":21993391.103},{"v":21546003.177999996},{"v":21546003.177999996},{"v":"#1e8449"},{"v":null}]},
      {"c":[{"v":"Travel"},{"v":null},{"v":null},{"v":21546003.177999996},{"v":21546003.177999996},{"v":21533258.930999994},{"v":21533258.930999994},{"v":"#1e8449"},{"v":null}]},
      {"c":[{"v":"Training"},{"v":null},{"v":null},{"v":21533258.930999994},{"v":21533258.930999994},{"v":21550964.529999994},{"v":21550964.529999994},{"v":"#922b21"},{"v":null}]},
      {"c":[{"v":"Actual"},{"v":null},{"v":null},{"v":0},{"v":0},{"v":21550964.52999999},{"v":21550964.52999999},{"v":"#007fff"},{"v":null}]},

      // bar (series 3) data
      {"c":[{"v":"FCST"},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null},{"v":16182561.25}]}
    ]
  });

  var comboChart = new google.visualization.ChartWrapper({
    chartType: 'ComboChart',
    containerId: 'chart_div',
    dataTable: dataChart,
    options: {
      animation: {
        duration: 1500,
        easing: 'inAndOut',
        startup: true
      },
      backgroundColor: 'transparent',
      bar: {
        group: {
          width: '85%'
        }
      },
      chartArea: {
        backgroundColor: 'transparent',
        bottom: 42,
        height: '100%',
        left: 60,
        top: 24,
        width: '100%'
      },
      hAxis: {
        textStyle: {
          fontSize: 12
        }
      },
      height: 400,
      legend: 'none',
      seriesType: 'bars',
      series: {
        2: {
          type: 'candlesticks'
        }
      },
      tooltip: {
        isHtml: true,
        trigger: 'both'
      },
      vAxis: {
        format: 'short',
        textStyle: {
          color: '#616161'
        },
        viewWindow: {
          min: 10000000,
          max: 24000000
        }
      },
      width: '100%'
    }
  });
  comboChart.draw();
}
<script src="https://www.gstatic.com/charts/loader.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<div id="chart_div"></div>

note: you'll notice when running the snippet in full page mode,
the chart doesn't do a very good job of aligning the x-axis labels
you may be able to manipulate the options to get them to align better,
but it will take some manipulation...

  1. you might have better results, creating three separate charts, side-by-side
    just need to make sure each has the same vAxis values,
    you could hide the extra vAxis labels if needed, etc...
    again, manipulation is required

example: snippet displayed best in full page mode... 

google.charts.load('current', {
  callback: function () {
    drawChart();
    $(window).resize(drawChart);
  },
  packages:['corechart', 'table']
});

function drawChart() {
  var dataBar0 = new google.visualization.DataTable({
    "cols":[
      {"label":"Category","type":"string"},
      {"label":"Bar 0","type":"number"},
      {"label":"Bar 1","type":"number"}
    ],
    "rows":[
      // bar 0 data
      {"c":[{"v":"YTD"},{"v":22807893.054},{"v":18684139.912}]}
    ]
  });

  var barChart0 = new google.visualization.ChartWrapper({
    chartType: 'ColumnChart',
    containerId: 'bar_chart_0',
    dataTable: dataBar0,
    options: {
      animation: {
        duration: 1500,
        easing: 'inAndOut',
        startup: true
      },
      backgroundColor: 'transparent',
      bar: {
        gap: 20,
        width: 60
      },
      chartArea: {
        backgroundColor: 'transparent',
      },
      height: 400,
      legend: 'none',
      theme: 'maximized',
      tooltip: {
        isHtml: true,
        trigger: 'both'
      },
      vAxis: {
        format: 'short',
        viewWindow: {
          min: 10000000,
          max: 24000000
        }
      }
    }
  });
  barChart0.draw();

  var dataWF0 = new google.visualization.DataTable({
    "cols":[
      {"label":"Category","type":"string"},
      {"label":"WF 2 - Bottom 1","type":"number"},
      {"label":"WF 2 - Bottom 2","type":"number"},
      {"label":"WF 2 - Top 1","type":"number"},
      {"label":"WF 2 - Top 2","type":"number"},
      {"role":"style","type":"string","p":{"role":"style"}}
    ],
    "rows":[
      {"c":[{"v":"Budget"},{"v":0},{"v":0},{"v":22707893.613},{"v":22707893.613},{"v":"#007fff"}]},
      {"c":[{"v":"Other"},{"v":22800059.612},{"v":22800059.612},{"v":21993391.103},{"v":21993391.103},{"v":"#1e8449"}]},
      {"c":[{"v":"Labor"},{"v":21993391.103},{"v":21993391.103},{"v":21546003.177999996},{"v":21546003.177999996},{"v":"#1e8449"}]},
      {"c":[{"v":"Travel"},{"v":21546003.177999996},{"v":21546003.177999996},{"v":21533258.930999994},{"v":21533258.930999994},{"v":"#1e8449"}]},
      {"c":[{"v":"Training"},{"v":21533258.930999994},{"v":21533258.930999994},{"v":21550964.529999994},{"v":21550964.529999994},{"v":"#922b21"}]},
      {"c":[{"v":"Actual"},{"v":0},{"v":0},{"v":21550964.52999999},{"v":21550964.52999999},{"v":"#007fff"}]}
    ]
  });

  var wfChart0 = new google.visualization.ChartWrapper({
    chartType: 'CandlestickChart',
    containerId: 'wf_chart_0',
    dataTable: dataWF0,
    options: {
      animation: {
        duration: 1500,
        easing: 'inAndOut',
        startup: true
      },
      backgroundColor: 'transparent',
      bar: {
        gap: 20,
        width: 60
      },
      chartArea: {
        backgroundColor: 'transparent'
      },
      height: 400,
      legend: 'none',
      theme: 'maximized',
      tooltip: {
        isHtml: true,
        trigger: 'both'
      },
      vAxis: {
        format: 'short',
        viewWindow: {
          min: 10000000,
          max: 24000000
        }
      },
      width: '100%'
    }
  });
  wfChart0.draw();

  var dataBar1 = new google.visualization.DataTable({
    "cols":[
      {"label":"Category","type":"string"},
      {"label":"Bar 0","type":"number"},
      {"label":"Bar 1","type":"number"}
    ],
    "rows":[
      // bar 0 data
      {"c":[{"v":"YTD"},{"v":20807893.054},{"v":19684139.912}]}
    ]
  });

  var barChart1 = new google.visualization.ChartWrapper({
    chartType: 'ColumnChart',
    containerId: 'bar_chart_1',
    dataTable: dataBar1,
    options: {
      animation: {
        duration: 1500,
        easing: 'inAndOut',
        startup: true
      },
      backgroundColor: 'transparent',
      bar: {
        gap: 20,
        width: 60
      },
      chartArea: {
        backgroundColor: 'transparent',
      },
      height: 400,
      legend: 'none',
      theme: 'maximized',
      tooltip: {
        isHtml: true,
        trigger: 'both'
      },
      vAxis: {
        format: 'short',
        viewWindow: {
          min: 10000000,
          max: 24000000
        }
      }
    }
  });
  barChart1.draw();
}
.max-chart {
  display: inline-block;
}
<script src="https://www.gstatic.com/charts/loader.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<div class="max-chart" id="bar_chart_0"></div>
<div class="max-chart" id="wf_chart_0"></div>
<div class="max-chart" id="bar_chart_1"></div>