Re: Null values in a column

[asgallant]

To put nulls in your data set, use the word null without any quotes, ie: 

data.addRows([
    [0, 2],
    [1, 1],
    [2, null],
    [3, 2],
    [4, 1]
]);

To the best of my knowledge, there is no good way to distinguish between zero values and nulls in some charts (particularly column, bar, and pie charts).  Line and area charts leave gaps in the lines/areas where there is a null data point by default (which can be overridden by setting the "interpolateNulls" option to true).  Scatter and bubble charts draw points when a value is zero and don't when the value is null.

Hmm...with bar and column charts, if you have multiple series of data, you might be able to distinguish nulls by setting the "focusTarget" option to "category".  This will add all data points at a given axis value to the tooltip when you hover over any one of the bars.  Null values are excluded from the tooltip, but zero values are included.  See example here: http://jsfiddle.net/asgallant/kLURn/

On Wednesday, September 26, 2012 10:53:28 AM UTC-4, Andy Hickey wrote:

A search of the forums reveals others who have grappled with null values in charts with varied success, but none seem to address the issue I am facing.

I have data with zeroes and null data. Zeroes mean a definite zero values, where as null means 'no data available'. I'm looking for a way to represent this on the chart. Obviously a zero should be a bar with zero height, but I'd like some kind of signifier of null data, so that those viewing the chart can tell the difference between a zero value and missing data ranges.

One thought I had thought of was to translate the null into a negative number and somehow get the chart to refuse to display data going down, but somehow else identify these ranges as being null. Rather vague I agree. 

It seems strange that I should be the first to tackle this issue, so if anyone has some ideas, I'd be very appreciative.

Regards

Andy

nb. Just putting null data in my charts seems to be an issue for me. When I place 'null' , or '_', which I've seen others use to represent NULL, I occasionally get the 'data must be of the same type' error. Changing nothing else other than whatever my null identifier is to a '0' or empty (,,) fixes the issue- is there one definite symbol to use for NULL?

[Andy Hickey]

Thanks asgallant,

I've added the category focus, and that helps a bit, but I'm still having problems with nulls in my data set, getting the error about everything needs to be the same data type

['Year','Health (P)','Health (A)','Gender (P)','Gender (A)','Education (P)','Education (A)','Agriculture (P)','Agriculture (A)','Social protection (P)','Social protection (A)','Environment (P)','Environment (A)','Water/sanitation (P)','Water/sanitation (A)'],

['2008',81.06,null,1.32,null,94.68,0,13.41,null,30.36,null,19.78,null,36.87,null],

['2009',27.13,null,22.34,null,33.6,null,79.92,null,1.34,null,89.77,0,15.68,null],

['2010',104.89,0,14.61,null,33.46,null,30.29,null,22.28,null,107.81,null,1.39,null],

['2011',55,0,110.69,null,1.3,null,106.24,0,14.45,null,27.34,null,30.71,null],

['2012',29.96,null,27.88,null,44.77,null,133.83,null,1.31,null,105.01,null,17.83,null],

['2013',0,null,0,null,0,null,0,null,0,null,0,null,0,null]

Results in an error, but replace those values with a zero and it's fine. What am I missing?

[asgallant]

This is a shortcoming of the arrayToDataTable method.  Your first row of data must have data of the correct type in all columns, and must not use the {v: value, f: 'formatted value'} syntax.  Since you have null values in your first row, the API is either assuiming that the data type of that column in null or it is throwing an error about not getting a valid data type.  To fix this, you will have to switch to the standard DataTable constructor:

var data = new google.visualization.DataTable();
data.addColumn('string', 'Year');
data.addColumn('number', 'Health (P)');
data.addColumn('number', 'Health (A)');
data.addColumn('number', 'Gender (P)');
data.addColumn('number', 'Gender (A)');
data.addColumn('number', 'Education (P)');
data.addColumn('number', 'Education (A)');
data.addColumn('number', 'Agriculture (P)');
data.addColumn('number', 'Agriculture (A)');
data.addColumn('number', 'Social protection (P)');
data.addColumn('number', 'Social protection (A)');
data.addColumn('number', 'Environment (P)');
data.addColumn('number', 'Environment (A)');
data.addColumn('number', 'Water/sanitation (P)');
data.addColumn('number', 'Water/sanitation (A)');
data.addRows([

    ['2008',81.06,null,1.32,null,94.68,0,13.41,null,30.36,null,19.78,null,36.87,null],
    ['2009',27.13,null,22.34,null,33.6,null,79.92,null,1.34,null,89.77,0,15.68,null],
    ['2010',104.89,0,14.61,null,33.46,null,30.29,null,22.28,null,107.81,null,1.39,null],
    ['2011',55,0,110.69,null,1.3,null,106.24,0,14.45,null,27.34,null,30.71,null],
    ['2012',29.96,null,27.88,null,44.77,null,133.83,null,1.31,null,105.01,null,17.83,null],
    ['2013',0,null,0,null,0,null,0,null,0,null,0,null,0,null]

]);

You can also build the DataTable as a JSON string and pass the string to the DataTable constructor (see the "examples" section of the DataTable class).

[Chris Powick]

Massive necro thread I know but this was the first one I stumbled on in a search.

You can now just infer data types for columns in the original column information, so for the OPs post you now have the line:

[{label: 'Year', type: 'number'},{label: 'Health (P)', type: 'number'},{label: 'Health (A)', type: 'number'},{label: 'Gender (P)', type: 'number'},{label: 'Gender (A)', type: 'number'},{label: 'Education (P)', type: 'number'},{label: 'Education (A)', type: 'number'},{label: 'Agriculture (P)', type: 'number'},{label: 'Agriculture (A)', type: 'number'},{label: 'Social protection (P)', type: 'number'},{label: 'Social protection (A)', type: 'number'}, {label: 'Environment (P)', type: 'number'},{ label: 'Environment (A)', type: 'number'},{label: 'Water/sanitation (P)', type: 'number'}, {label: 'Water/sanitation (A)', type: 'number'}],

You can also use this in conjunction with any data type (not just numbers) as well as annotation columns {role: 'annotation', type: 'number'} etc.