I have data for a multiple choice (checkboxes) question - the data is structured in such a way that a person can select none, one or more than one option and so the data is saved in multiple columns. I am trying to build a google chart using control wrappers so that its filterable but at the same time I want to chart the data using the percentage instead of counts.. and thats the tricky part, because the base (or denominator) will be the total number of people answering the question and not the total number of active rows in the file.. so in my data, the base / denominator is 8 (as only 8 people have answered this question, 2 have left it blank / not answered) and I want the counts of each yes in each column to be divided by 8 in order to calculate the percentage of each option and then chart all options in one bar chart. The data is in this spreadssheet: https://docs.google.com/spreadsheets/d/19VWNZkHG5GEuYCibDmtOlKblKiOWcx94Wi9jyuhvEUo/edit#gid=0
I have also kept the desired output chart in the same sheet. @WhiteHat helped me to aggregate data (and I am very thankful for him guiding me in the right direction here!!!) in a data view but when I created a new data view which has the desired columns, my chart doesnt draw - I get the error message "All series on a given axis must be of the same data type".
My code so 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 for All 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 a bar chart to 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 a "ready" event handler for proxyTable the handles data aggregation and drawing barChart
// Add The question's column index here. We want to draw Status so we Group 2 with dt and also its 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, the data will be sorted by column 0, then 1, then 2
// if you want a different order, you have to re-sort
barChart.setDataTable(view2);
barChart.draw();
});
// Create the dashboard.
new google.visualization.Dashboard(document.getElementById('dashboard')).
// Configure the controls :
bind(CardTier, Campaign).
bind(Campaign, proxyTable).
// Draw the 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 you for any help in advance!!!
