javascript - Google Bar Chart with Percentage of Total and with Category Filters -
i new google charts , trying create bar chart % of total, along ability filter data using google dashboard controls... followed (thanks @asgallant this!) google.visualization.chartwrapper group columns view , able bar chart picks data google sheet, , draws chart counts , have ability filter data using google category filters.
however, stuck - when try add columns (dataview) calculating total (so can draw chart using percentage , show percentage in bar labels) - chart still drawing using counts.. can please let me know going wrong here:
function drawvisualization() { var query = new google.visualization.query('https://docs.google.com/spreadsheets/d/1lbos8q7qdpwvjks3fytqaefthzy3vbahllf04ne6qo8/edit?gid=1629614877&range=a:d'); query.send(handlequeryresponse); function handlequeryresponse(response) { if (response.iserror()) {return; } var data = response.getdatatable(); // define category pickers filters var cardtier = new google.visualization.controlwrapper({ 'controltype': 'categoryfilter', 'containerid': 'control1', 'options': { 'filtercolumnlabel': 'cardtier filter', 'ui': { 'labelstacking': 'vertical', 'allowtyping': false, 'allowmultiple': false } } }); var campaign = new google.visualization.controlwrapper({ 'controltype': 'categoryfilter', 'containerid': 'control2', 'options': { 'filtercolumnlabel': 'campaign filter', 'ui': { 'labelstacking': 'vertical', 'allowtyping': false, 'allowmultiple': false } } }); // define bar chart show 'population' data var barchart = new google.visualization.chartwrapper({ 'charttype': 'barchart', 'containerid': 'chart1', 'options': { 'width': 400, 'height': 300, 'chartarea': {top: 0, right: 0, bottom: 0} }, // configure barchart use columns 0 (card tier) , 1 (campaign filter) (basically filters) 'view': {'columns': [0, 1]} }); var proxytable = new google.visualization.chartwrapper({ charttype: 'table', containerid: 'proxytable', options: { // minimize footprint of table in html page: 'enable', pagesize: 1 }, view: { columns: [0] } }); // create "ready" event handler proxytable handles data aggregation , drawing barchart // add question's column index here. want draw status group 2 dt , count... google.visualization.events.addlistener(proxytable, 'ready', function () { var dt = proxytable.getdatatable(); var groupeddata = google.visualization.data.group(dt, [2], [{ column: 3, type: 'number', label: dt.getcolumnlabel(2), aggregation: google.visualization.data.count }]); var view = new google.visualization.dataview(groupeddata); view.setcolumns([0, 1, { calc: function (dt, row) { var amount = formatshort.formatvalue(dt.getvalue(row, 1)); var percent = formatpercent.formatvalue(dt.getvalue(row, 1) / groupeddata.getvalue(0, 1)); return amount + ' (' + percent + ')'; }, type: 'string', role: 'annotation' }]); // after grouping, data sorted column 0, 1, 2 // if want different order, have re-sort barchart.setdatatable(view); barchart.draw(); }); // create dashboard. new google.visualization.dashboard(document.getelementbyid('dashboard')). // configure controls : bind(cardtier, campaign). bind(campaign, proxytable). // draw dashboard draw(data); } } google.load('visualization', '1', {packages:['corechart', 'controls', 'table'], callback: drawvisualization}); </script> </head> <body> <div id="dashboard"> <table> <tr style='vertical-align: top'> <td style='width: 300px; font-size: 0.9em;'> <div id="control1"></div> <div id="control2"></div> </td> <td style='width: 600px'> <div style="float: left;" id="chart1"></div> <div style="float: left;" id="chart2"></div> </td> </tr> </table> <div id="proxytable" style="display: none;"></div> </div> </body> </html>
for starters, recommend using newer library loader.js
<script src="https://www.gstatic.com/charts/loader.js"></script> instead of jsapi, according release notes...
the version of google charts remains available via
jsapiloader no longer being updated consistently. please use new gstatic loader on.
this change load statement, see following working snippet...
next, didn't see definitions number formatters
formatshort , formatpercent
need add
groupeddata give total each status
the total rows,
need use modifier function
change value 'total' first column of rows
allowing group method aggregate rows
var totaldata = google.visualization.data.group( datatable, [{column: 0, type: 'string', modifier: function () {return 'total';}}], [{ column: 3, type: 'number', label: datatable.getcolumnlabel(2), aggregation: google.visualization.data.count }] ); finally, remove view option barchart
since we're providing view want drawn
see following working snippet...
google.charts.load('current', { callback: drawvisualization, packages: ['corechart', 'controls', 'table'] }); function drawvisualization() { var query = new google.visualization.query('https://docs.google.com/spreadsheets/d/1lbos8q7qdpwvjks3fytqaefthzy3vbahllf04ne6qo8/edit?gid=1629614877&range=a:d'); query.send(handlequeryresponse); function handlequeryresponse(response) { if (response.iserror()) {return; } var data = response.getdatatable(); // define category pickers filters var cardtier = new google.visualization.controlwrapper({ 'controltype': 'categoryfilter', 'containerid': 'control1', 'options': { 'filtercolumnlabel': 'cardtier filter', 'ui': { 'labelstacking': 'vertical', 'allowtyping': false, 'allowmultiple': false } } }); var campaign = new google.visualization.controlwrapper({ 'controltype': 'categoryfilter', 'containerid': 'control2', 'options': { 'filtercolumnlabel': 'campaign filter', 'ui': { 'labelstacking': 'vertical', 'allowtyping': false, 'allowmultiple': false } } }); // define bar chart show 'population' data var barchart = new google.visualization.chartwrapper({ 'charttype': 'barchart', 'containerid': 'chart1', 'options': { 'width': 400, 'height': 300, 'chartarea': {top: 0, right: 0, bottom: 0} } }); var proxytable = new google.visualization.chartwrapper({ charttype: 'table', containerid: 'proxytable', options: { // minimize footprint of table in html page: 'enable', pagesize: 1 }, view: { columns: [0] } }); // create "ready" event handler proxytable handles data aggregation , drawing barchart // add question's column index here. want draw status group 2 dt , count... google.visualization.events.addlistener(proxytable, 'ready', function () { var formatshort = new google.visualization.numberformat({ pattern: 'short' }); var formatpercent = new google.visualization.numberformat({ pattern: '0.0%' }); var datatable = proxytable.getdatatable(); // group status var groupeddata = google.visualization.data.group( datatable, [2], [{ column: 3, type: 'number', label: datatable.getcolumnlabel(2), aggregation: google.visualization.data.count }] ); // status total var totaldata = google.visualization.data.group( datatable, [{column: 0, type: 'string', modifier: function () {return 'total';}}], [{ column: 3, type: 'number', label: datatable.getcolumnlabel(2), aggregation: google.visualization.data.count }] ); var view = new google.visualization.dataview(groupeddata); view.setcolumns([0, 1, { calc: function (dt, row) { var amount = dt.getvalue(row, 1); var total = totaldata.getvalue(0, 1); var percent = 0; if (total > 0) { percent = amount / total; } return formatshort.formatvalue(amount) + ' (' + formatpercent.formatvalue(percent) + ')'; }, type: 'string', role: 'annotation' }]); // after grouping, data sorted column 0, 1, 2 // if want different order, have re-sort barchart.setdatatable(view); barchart.draw(); }); // create dashboard. new google.visualization.dashboard(document.getelementbyid('dashboard')). // configure controls : bind(cardtier, campaign). bind(campaign, proxytable). // draw dashboard draw(data); } }<script src="https://www.gstatic.com/charts/loader.js"></script> <div id="control1"></div> <div id="control2"></div> <div id="chart1"></div> <div id="proxytable"></div>update
to draw percentages instead of counts,
need add calculated column view
as showing 0 values,
use original data table distinct list of status values
check if status exists in groupeddata
if not, add row status
// add missing status var statusvalues = data.getdistinctvalues(2); statusvalues.foreach(function (status) { var statusrow = groupeddata.getfilteredrows([{ column: 0, value: status }]); if (statusrow.length === 0) { groupeddata.addrow([ status, 0 ]); } }); groupeddata.sort([{column: 0}]); see following working snippet...
google.charts.load('current', { callback: drawvisualization, packages: ['corechart', 'controls', 'table'] }); function drawvisualization() { var query = new google.visualization.query('https://docs.google.com/spreadsheets/d/1lbos8q7qdpwvjks3fytqaefthzy3vbahllf04ne6qo8/edit?gid=1629614877&range=a:d'); query.send(handlequeryresponse); function handlequeryresponse(response) { if (response.iserror()) {return; } var data = response.getdatatable(); // define category pickers filters var cardtier = new google.visualization.controlwrapper({ 'controltype': 'categoryfilter', 'containerid': 'control1', 'options': { 'filtercolumnlabel': 'cardtier filter', 'ui': { 'labelstacking': 'vertical', 'allowtyping': false, 'allowmultiple': false } } }); var campaign = new google.visualization.controlwrapper({ 'controltype': 'categoryfilter', 'containerid': 'control2', 'options': { 'filtercolumnlabel': 'campaign filter', 'ui': { 'labelstacking': 'vertical', 'allowtyping': false, 'allowmultiple': false } } }); // define bar chart show 'population' data var barchart = new google.visualization.chartwrapper({ 'charttype': 'barchart', 'containerid': 'chart1', 'options': { 'width': 400, 'height': 300, 'chartarea': {top: 0, right: 0, bottom: 0} } }); var proxytable = new google.visualization.chartwrapper({ charttype: 'table', containerid: 'proxytable', options: { // minimize footprint of table in html page: 'enable', pagesize: 1 }, view: { columns: [0] } }); // create "ready" event handler proxytable handles data aggregation , drawing barchart // add question's column index here. want draw status group 2 dt , count... google.visualization.events.addlistener(proxytable, 'ready', function () { var formatshort = new google.visualization.numberformat({ pattern: 'short' }); var formatpercent = new google.visualization.numberformat({ pattern: '0.0%' }); var datatable = proxytable.getdatatable(); // group status var groupeddata = google.visualization.data.group( datatable, [2], [{ column: 3, type: 'number', label: datatable.getcolumnlabel(2), aggregation: google.visualization.data.count }] ); // add missing status var statusvalues = data.getdistinctvalues(2); statusvalues.foreach(function (status) { var statusrow = groupeddata.getfilteredrows([{ column: 0, value: status }]); if (statusrow.length === 0) { groupeddata.addrow([ status, 0 ]); } }); groupeddata.sort([{column: 0}]); // status total var totaldata = google.visualization.data.group( datatable, [{column: 0, type: 'string', modifier: function () {return 'total';}}], [{ column: 3, type: 'number', label: datatable.getcolumnlabel(2), aggregation: google.visualization.data.count }] ); var view = new google.visualization.dataview(groupeddata); view.setcolumns([0, { calc: function (dt, row) { var amount = dt.getvalue(row, 1); var total = totaldata.getvalue(0, 1); var percent = 0; if (total > 0) { percent = amount / total; } return { v: percent, f: formatpercent.formatvalue(percent) }; }, type: 'number', label: 'percent' }, { calc: function (dt, row) { var amount = dt.getvalue(row, 1); var total = totaldata.getvalue(0, 1); var percent = 0; if (total > 0) { percent = amount / total; } return formatpercent.formatvalue(percent) + ' (' + formatshort.formatvalue(amount) + ')'; }, type: 'string', role: 'annotation' }]); // after grouping, data sorted column 0, 1, 2 // if want different order, have re-sort barchart.setdatatable(view); barchart.draw(); }); // create dashboard. new google.visualization.dashboard(document.getelementbyid('dashboard')). // configure controls : bind(cardtier, campaign). bind(campaign, proxytable). // draw dashboard draw(data); } }<script src="https://www.gstatic.com/charts/loader.js"></script> <div id="control1"></div> <div id="control2"></div> <div id="chart1"></div> <div id="proxytable"></div>update 2
to find total of multiple choice question,
create view calculated column
new column should test question columns not blank
total view on calculated column
see following working snippet...
google.charts.load('current', { callback: drawvisualization, packages: ['corechart', 'controls', 'table'] }); function drawvisualization() { var query = new google.visualization.query('https://docs.google.com/spreadsheets/d/19vwnzkhg5geuycibdmtolkblkiowcx94wi9jyuhveuo/edit#gid=0'); query.setquery('select a,b,c,d,e,f,g'); query.send(handlequeryresponse); function handlequeryresponse(response) { if (response.iserror()) {return;} var data = response.getdatatable(); var view = new google.visualization.dataview(data); view.setcolumns([0, 1, 2, 3, 4, 5, 6, { calc: function (dt, row) { var answered = 0; var q1_1 = dt.getvalue(row, 3) || ''; var q1_2 = dt.getvalue(row, 4) || ''; var q1_3 = dt.getvalue(row, 5) || ''; var q1_4 = dt.getvalue(row, 6) || ''; if ((q1_1 !== '') || (q1_2 !== '') || (q1_3 !== '') || (q1_4 !== '')) { answered = 1; } return answered; }, label: 'answered', type: 'number' }]); var totalanswered = google.visualization.data.group( view, [{column: 0, type: 'string', modifier: function () {return 'total';}}], [{ column: view.getnumberofcolumns() - 1, type: 'number', label: view.getcolumnlabel(view.getnumberofcolumns() - 1), aggregation: google.visualization.data.sum }] ); var proxytable = new google.visualization.chartwrapper({ charttype: 'table', containerid: 'proxytable', datatable: view }); proxytable.draw(); document.getelementbyid('proxytabletotal').innerhtml = 'total answered = ' + totalanswered.getvalue(0, 1); } }<script src="https://www.gstatic.com/charts/loader.js"></script> <div id="proxytable"></div> <div id="proxytabletotal"></div>
Comments
Post a Comment