javascript - Google Chart using Control Wrapper for Multiple Choice Questions -
i have data multiple choice (checkboxes) question - data structured in such way person can select none, 1 or more 1 option , data saved in multiple columns. trying build google chart using control wrappers filterable @ same time want chart data using percentage instead of counts.. , thats tricky part, because base (or denominator) total number of people answering question , not total number of active rows in file.. in data, base / denominator 8 (as 8 people have answered question, 2 have left blank / not answered) , want counts of each yes in each column divided 8 in order calculate percentage of each option , chart options in 1 bar chart. data in spreadssheet: https://docs.google.com/spreadsheets/d/19vwnzkhg5geuycibdmtolkblkiowcx94wi9jyuhveuo/edit#gid=0
i have kept desired output chart in same sheet. @whitehat helped me aggregate data (and thankful him guiding me in right direction here!!!) in data view when created new data view has desired columns, chart doesnt draw - error message "all series on given axis must of same data type".
my code far is:
<!doctype html> <html> <head> <base target="_top"> <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"> <script src="https://www.gstatic.com/charts/loader.js"></script> <script type="text/javascript"> function drawvisualization() { var query = new google.visualization.query('https://docs.google.com/spreadsheets/d/19vwnzkhg5geuycibdmtolkblkiowcx94wi9jyuhveuo/edit?gid=0&range=a:h'); 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': true } } }); var campaign = new google.visualization.controlwrapper({ 'controltype': 'categoryfilter', 'containerid': 'control2', 'options': { 'filtercolumnlabel': 'campaign filter', 'ui': { 'labelstacking': 'vertical', 'allowtyping': false, 'allowmultiple': true } } }); // define bar chart show 'population' data var barchart = new google.visualization.chartwrapper({ 'charttype': 'columnchart', 'containerid': 'chart1', 'options': options_column }); 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); // 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(); var view2 = new google.visualization.dataview(proxytable); view.setcolumns([3, 4, 5, 6, { calc: function (dt, row) { var amount = dt.getvalue(row, 1); var percent = 0; if (totalanswered.getvalue(0, 1) > 0) { percent = amount / totalanswered.getvalue(0, 1); } return { v: percent, f: formatpercent.formatvalue(percent) }; }, type: 'number', label: 'percent' }, { calc: function (dt, row) { var amount = dt.getvalue(row, 1); var percent = 0; if (totalanswered.getvalue(0, 1) > 0) { percent = amount / totalanswered.getvalue(0, 1); } return 'n=' + 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(view2); 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" ></div> <div id="proxytabletotal" ></div> </div> </body> </html> thank in advance!!!
to "yes" counts each column,
can include columns in our aggregation,
after creating view
here, custom agg function used count "yes" values --> countyes
then new data table created,
each count column added row,
percent of total answered
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&range=a:h'); 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': true } } }); var campaign = new google.visualization.controlwrapper({ 'controltype': 'categoryfilter', 'containerid': 'control2', 'options': { 'filtercolumnlabel': 'campaign filter', 'ui': { 'labelstacking': 'vertical', 'allowtyping': false, 'allowmultiple': true } } }); // define bar chart show 'population' data var barchart = new google.visualization.chartwrapper({ 'charttype': 'columnchart', 'containerid': 'chart1' }); var proxytable = new google.visualization.chartwrapper({ charttype: 'table', containerid: 'proxytable', options: { // minimize footprint of table in html page: 'enable', pagesize: 1 }, view: { columns: [1] } }); // 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(); // build view calc answered var view = new google.visualization.dataview(datatable); 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' }]); // build agg columns each choice , answered var aggcolumns = []; (var = 3; < view.getnumberofcolumns(); i++) { var aggfunc; if (i < (view.getnumberofcolumns() - 1)) { aggfunc = countyes; } else { aggfunc = google.visualization.data.sum; } aggcolumns.push({ column: i, type: 'number', label: view.getcolumnlabel(i), aggregation: aggfunc }); } // function count yes values function countyes(values) { var yes = 0; values.foreach(function (value) { if (value === 'yes') { yes++; } }); return yes; } // aggregate view var totalanswered = google.visualization.data.group( view, [{column: 0, type: 'string', modifier: function () {return 'total';}}], aggcolumns ); // data table bar chart var choicedata = new google.visualization.datatable(); choicedata.addcolumn('string', 'choice'); choicedata.addcolumn('number', 'percent'); choicedata.addcolumn({role: 'annotation', type: 'string'}); // add row each column in aggregation (var = 1; < totalanswered.getnumberofcolumns() - 1; i++) { var value = totalanswered.getvalue(0, i); var total = totalanswered.getvalue(0, totalanswered.getnumberofcolumns() - 1); var percent = 0; if (total > 0) { percent = value / total; } choicedata.addrow([ totalanswered.getcolumnlabel(i), { v: percent, f: formatpercent.formatvalue(percent) }, formatpercent.formatvalue(percent) + ' (' + formatshort.formatvalue(value) + ')' ]); } barchart.setdatatable(choicedata); 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="dashboard"></div> <div id="control1"></div> <div id="control2"></div> <div id="chart1"></div> <div id="proxytable"></div> <div id="proxytabletotal"></div>
Comments
Post a Comment