Google Charts API

Calculating Values

Introduction

This page was written because José asked in the in the API user-supported group how to do calculations on an entire column.

For this page, several types of calculations that will be discussed. Row calculations, that is taking a row of data and putting the the result of a calculation on values in that row into a new column. There are several methods I can think of for doing this, specific to this type of calculation is to use the built-in calc function

Then there are calculations to be done on an entire column such as sum, average, mode, median, etc. There are several methods I can think of for doing this, specific to this type of calculation is to use the built-in aggregator function. To use this you must have a column which contains the key to group the data by, even if the key is the same for all data rows to be able to include them all, and that sometimes has to be added to the datatable while or after the data is being imported

Common to both row and column calculations is a second method and that is to step through the datatable using a FOR loop and doing the calculation yourself using JavaScript. A common third method is to alter the data before it gets imported into the API using whatever language you are most comfortable with, PHP, SQL, and so on.

Loops

Dataviews are read only and so there are no set data methods available to them, datatables have various methods to both read and write to them. So, this method can be used get get data from a datatable or dataview, but only write it to a datatable.

Suppose you have a datatable named dataTable and just need the total of a column of numbers to use outside the API and it does not need to be written back to the datatable. In this example, the numbers to total are in column 3.


var totalRows = dataTable.getNumberOfRows();
var dataCount = 0;

for (i = 0; i < totalRows; i++) { 
	var dataCount += dataTable.getValue(i, 3);
}

The variables dataRows and dataCount now be used in further calculations, written to the page and so on.

For the pruposes of this page the data comes from a Google Sheet which shows the sex of students in a small college for various semesters. There is documentation for using Google Sheets and column charts in the API.

The code to draw the above chart is:


<script type="text/javaScript">
var students = (function() {
	// create namespace global variable to hold the data
    google.charts.load('current', {'packages':['corechart']});
    google.charts.setOnLoadCallback(drawCharts);
	
    function drawCharts() {
		var queryString = encodeURIComponent('SELECT B,C,D ORDER BY A');
		var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=1786091632&headers=1&tq=' + queryString);
      query.send(handleQueryResponse);
    }

    function handleQueryResponse(response) {
      if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
      }

    var studentData = response.getDataTable();
		
	var studentChart = new google.visualization.ColumnChart(document.getElementById('studentData_div'));
   	studentChart.draw(studentData);
	}
	})();
</script>

Calc Function

What the calc function can be used for is to add a calculated column while a is dataview is being created from a datatable using setColumns. Suppose just the total amount of students for each semester are needed instead of being split into sex. As the data for both is in the original datatable, the dataview just needs the sum of those two columns and this can be calculated using:


<script type="text/javaScript">
var allStudentView = new google.visualization.DataView(studentData);
allStudentView.setColumns([0,{calc:allStudents, type:'number', label:'All Students'}]);
function allStudents(studentData, rowNum){
	return studentData.getValue(rowNum, 1) + studentData.getValue(rowNum, 2);
}
</script>

The return from the function can be anything, not just a calculation from the original datatable. It could be a variable calculated elsewhere, a number, or a piece of text. The calc function help is in the API documentation - see under setColumns(columnIndexes) in Dataview Contructors

The new dataview, allStudentView, can be drawn instead of the original datatable to produce:

The code to draw the above chart is:


<script type="text/javaScript">
var students = (function() {
	// create namespace global variable to hold the data
    google.charts.load('current', {'packages':['corechart']});
    google.charts.setOnLoadCallback(drawCharts);
	
    function drawCharts() {
		var queryString = encodeURIComponent('SELECT B,C,D ORDER BY A');
		var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=1786091632&headers=1&tq=' + queryString);
      query.send(handleQueryResponse);
    }

    function handleQueryResponse(response) {
      if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
      }

    var studentData = response.getDataTable();
		
	var allStudentView = new google.visualization.DataView(studentData);
	allStudentView.setColumns([0,{calc:allStudents, type:'number', label:'All Students'}]);
	function allStudents(studentData, rowNum){
 		return studentData.getValue(rowNum, 1) + studentData.getValue(rowNum, 2);
	}
	
	var allStudentsChart = new google.visualization.ColumnChart(document.getElementById('allStudents_div'));
	allStudentsChart.draw(allStudentView);
	}
	})();
</script>

Trendline

A trendline cannot be used on this chart because the major axis of the chart is discrete and not continuous. The API recognizes the columns types with a number, date, datetime and timeofday as continuous. As this data is using semesters, which the API does not recognize as a continuous data type, it is treated as discrete text.

Aggregation Functions

Grouping and Aggregation functions work on groups of data. The data the rows are grouped on needs to be included in the data. In order to group all the rows the grouping data must be the same for all of them. The current data does not have a column to group the data by. In order to do this the following must be completed:

Import the data into a new datatable.
Create a new table view with the grouping data and averages. As all the data is being used, the grouping data is all the same (in this case an empty string). Then use the aggregation function to calculate the averages.
Create the averages variables from the above table view - they are all going to be the same.
Make a new view from the original datatable and add the the calcuated averages to new columns.

After doing all of the above, the following chart is produced:

The code to draw the above chart is:


<script type="text/javaScript">
var students = (function() {
	// create namespace global variable to hold the data
    google.charts.load('current', {'packages':['corechart']});
    google.charts.setOnLoadCallback(drawCharts);
	
    function drawCharts() {
		var queryString = encodeURIComponent('SELECT B,C,D ORDER BY A');
		var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=1786091632&headers=1&tq=' + queryString);
      query.send(handleQueryResponse);
    }

    function handleQueryResponse(response) {
      if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
      }

    var studentData = response.getDataTable();
		
	var allStudentView = new google.visualization.DataView(studentData);
	allStudentView.setColumns([0,{calc:allStudents, type:'number', label:'All Students'}]);
	function allStudents(studentData, rowNum){
 		return studentData.getValue(rowNum, 1) + studentData.getValue(rowNum, 2);
	}
				
	//Create the min, max and average using the aggregation function in a new view
	var studentAggView = google.visualization.data.group(
    	allStudentView,
	// The first column is what to group on, in this case an empty string
    	[{column: 0, type: 'string', modifier: function () {return '';}}],
    	[
			{column: 1, type: 'number', aggregation: google.visualization.data.max},
			{column: 1, type: 'number', aggregation: google.visualization.data.avg},
			{column: 1, type: 'number', aggregation: google.visualization.data.min},
    	]
	);
		
	// Put the calculated aggregates into variables
	var studendMax = studentAggView.getValue(0, 1);
	var studentAve = studentAggView.getValue(0, 2);
	var studentMin = studentAggView.getValue(0, 3);
		
	// Add the aggregate variables using the calc function
	// The student totals have to be recalculated because the views are just a reference to the tables
    allStudentView.setColumns([0, {
	label:'Student #',
	type:'number',
	 calc: function (dt, row) {
		return dt.getValue(row,1) + dt.getValue(row,2);
    }
	},{
    label: 'Max Value',
    type: 'number',
    calc: function (dt, row) {
		return studendMax;
    }
}, {
    label: 'Avg Value',
    type: 'number',
    calc: function (dt, row) {
		return studentAve;
	}
}, {
    label: 'Min Value',
    type: 'number',
    calc: function (dt, row) {
		return studentMin;
    }
}]);	

	//Create the various options for the data to be drawn
	var options = {
		series: {
			0:{type: 'column'},
			1:{type: 'line'},
			2:{type: 'line'},
			3:{type: 'line'}
		}
	};
							   
	var aggChart = new google.visualization.ColumnChart(document.getElementById('aggStudents_div'));
	aggChart.draw(allStudentView,options);
		
	}
	})();
</script>

Google Charts API

Calculating Values

Introduction

Loops

Calc Function

Trendline

Aggregation Functions

This page created April 29, 2023; last modified May 7, 2023