Visualization Line Chart - Filter out blank cells after pivot or create DataView fed by source url
257 views
Skip to first unread message
leglera
May 31, 2012, 1:28:01 PM5/31/12
to Google Visualization API
Is there a way in a LineChart that is dependent on a CategoryPicker to
filter out blank cells?
I pivoted the data to get it in the correct format for a lineChart.
I have various measures of data that I want to graph on a LineChart,
but because there are blanks in the data the LineChart comes out with
blanks and doesn't display correctly. The same issue with the Table,
but I can't figure out how to filter out the blank cells in each view.
I have tried generating different DataViews but have had no luck
getting them to work with the sourceurl. Any suggestions would be
very appreciated.
<!--
You are free to copy and use this sample in accordance with the terms
of the
Apache license (http://www.apache.org/licenses/LICENSE-2.0.html)
-->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://
www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8"/
>
<title>
Google Visualization API Sample
</title>
<script type="text/javascript" src="http://www.google.com/jsapi"></
script>
<script type="text/javascript">
google.load('visualization', '1.1', {packages: ['controls']});
</script>
<script type="text/javascript">
function drawVisualization() {
// Prepare the data
var query = new google.visualization.Query(
'https://docs.google.com/spreadsheet/tq?
key=0AgInH0hsnKg_dHZaYjAyTXUtMU1wU1VqSlotS1ZHUnc&headers=1');
// Apply query language.
query.setQuery('SELECT A, B, C, avg(G) Group by A, B, C pivot
D');
// Send the query with a callback function.
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' +
response.getDetailedMessage());
return;
}
var data = response.getDataTable();
// Define a category picker control for the 'file' column
var filePicker = new google.visualization.ControlWrapper({
'controlType': 'CategoryFilter',
'containerId': 'control1',
'options': {
'filterColumnLabel': 'file',
'ui': {
'labelStacking': 'vertical',
'allowTyping': false,
'allowMultiple': false
}
}
});
// Define a category picker control for the 'by'
column
var byPicker = new google.visualization.ControlWrapper({
'controlType': 'CategoryFilter',
'containerId': 'control2',
'options': {
'filterColumnLabel': 'by',
'ui': {
'labelStacking': 'vertical',
'allowTyping': false,
'allowMultiple': false
}
}
});
//Define a line chart
var line = new google.visualization.ChartWrapper({
'chartType':'LineChart',
'containerId':'chart1',
'options': {
'title':'Population Density (people/km^2)',
'legend':'right'},
'width': '600px',
'view': {'columns': [3,4,5,6]},
});
// Define a table
var table = new google.visualization.ChartWrapper({
'chartType': 'Table',
'containerId': 'chart2',
'options': {
'width': '100%',
}
});
// Create a dashboard
new
google.visualization.Dashboard(document.getElementById('dashboard')).
// Establish bindings, declaring the both the slider and
the category
// picker will drive both charts.
bind(filePicker, byPicker).
bind([byPicker],[line, table]).
// Draw the entire dashboard.
draw(data);
}
google.setOnLoadCallback(drawVisualization);
</script>
</head>
<body style="font-family: Arial;border: 0 none;">
<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>
<div style="float: left;" id="chart3"></div>
</td>
</tr>
</table>
</div>
</body>
</html>
filter out blank cells?
I pivoted the data to get it in the correct format for a lineChart.
I have various measures of data that I want to graph on a LineChart,
but because there are blanks in the data the LineChart comes out with
blanks and doesn't display correctly. The same issue with the Table,
but I can't figure out how to filter out the blank cells in each view.
I have tried generating different DataViews but have had no luck
getting them to work with the sourceurl. Any suggestions would be
very appreciated.
<!--
You are free to copy and use this sample in accordance with the terms
of the
Apache license (http://www.apache.org/licenses/LICENSE-2.0.html)
-->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://
www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8"/
>
<title>
Google Visualization API Sample
</title>
<script type="text/javascript" src="http://www.google.com/jsapi"></
script>
<script type="text/javascript">
google.load('visualization', '1.1', {packages: ['controls']});
</script>
<script type="text/javascript">
function drawVisualization() {
// Prepare the data
var query = new google.visualization.Query(
'https://docs.google.com/spreadsheet/tq?
key=0AgInH0hsnKg_dHZaYjAyTXUtMU1wU1VqSlotS1ZHUnc&headers=1');
// Apply query language.
query.setQuery('SELECT A, B, C, avg(G) Group by A, B, C pivot
D');
// Send the query with a callback function.
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' +
response.getDetailedMessage());
return;
}
var data = response.getDataTable();
// Define a category picker control for the 'file' column
var filePicker = new google.visualization.ControlWrapper({
'controlType': 'CategoryFilter',
'containerId': 'control1',
'options': {
'filterColumnLabel': 'file',
'ui': {
'labelStacking': 'vertical',
'allowTyping': false,
'allowMultiple': false
}
}
});
// Define a category picker control for the 'by'
column
var byPicker = new google.visualization.ControlWrapper({
'controlType': 'CategoryFilter',
'containerId': 'control2',
'options': {
'filterColumnLabel': 'by',
'ui': {
'labelStacking': 'vertical',
'allowTyping': false,
'allowMultiple': false
}
}
});
//Define a line chart
var line = new google.visualization.ChartWrapper({
'chartType':'LineChart',
'containerId':'chart1',
'options': {
'title':'Population Density (people/km^2)',
'legend':'right'},
'width': '600px',
'view': {'columns': [3,4,5,6]},
});
// Define a table
var table = new google.visualization.ChartWrapper({
'chartType': 'Table',
'containerId': 'chart2',
'options': {
'width': '100%',
}
});
// Create a dashboard
new
google.visualization.Dashboard(document.getElementById('dashboard')).
// Establish bindings, declaring the both the slider and
the category
// picker will drive both charts.
bind(filePicker, byPicker).
bind([byPicker],[line, table]).
// Draw the entire dashboard.
draw(data);
}
google.setOnLoadCallback(drawVisualization);
</script>
</head>
<body style="font-family: Arial;border: 0 none;">
<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>
<div style="float: left;" id="chart3"></div>
</td>
</tr>
</table>
</div>
</body>
</html>
asgallant
May 31, 2012, 4:13:04 PM5/31/12
to google-visua...@googlegroups.com
You'll need to filter out the nulls before handing the data to the Dashboard. You can use a DataView to accomplish this:
var view = new google.visualization.DataView(data);
view.setRows(data.getFilteredRows(/* filter out nulls here */);
view.setRows(data.getFilteredRows(/* filter out nulls here */);
dashboard.draw(view);
This will filter out entire rows, though, so you lose any other data in those rows when the filter criteria is null. I would suggest avoiding this unless all relevant cells are null. If you are encountering a null cell here or there that is interrupting the flow of your charts, you may be better off setting the "interpolateNulls" option to true, which will make the charts estimate the curve of the line between two points with a null in between. Alternatively, you can use a calculated column to do your own interpolation.
This will filter out entire rows, though, so you lose any other data in those rows when the filter criteria is null. I would suggest avoiding this unless all relevant cells are null. If you are encountering a null cell here or there that is interrupting the flow of your charts, you may be better off setting the "interpolateNulls" option to true, which will make the charts estimate the curve of the line between two points with a null in between. Alternatively, you can use a calculated column to do your own interpolation.
Reply all
Reply to author
Forward
0 new messages