Threadid Threadid - 1 month ago 20
HTML Question

Google Table Chart Number Formatting displays NaN when using formatted numeric value with comma's

As often happens whenever Google has a major release of their charts library things break. The following code was implemented about 3 years ago and worked fine for a couple of years and within the last year it broke this way

I populate a Data Table and format the numbers with this code

var formatter = new google.visualization.NumberFormat({pattern:'#,###.##'});

var cl = jsonChartData.rows[0].c.length;
for (var c=0;c<cl;c++) {
if (jsonChartData.cols[c].type == 'number'){
formatter.format(popDataTable, c);
}
}


and this works well and produces a result like this:
v: 6155177.2549019605, f: '6,155,177.25'


And is supported by documentation here Google Table Chart, here Generic formatters, and here ICU 58.1

I populate the view that is passed to the chart object using the following code

var tabOpt = {
width: '100%'
,allowHtml: true
,cssClassNames: {
tableRow: 'chartTabRow'
,oddTableRow: 'chartTabRow'
,headerRow: 'chartHeadRow'
,tableCell: 'chartTabRow'
,headerCell: 'chartHeadRow'
}
};
var wdt = popDataTable;
wdt.sort([
{column: colID(wdt, 'sumlev')}
,{column: colID(wdt, 'estyear')}
,{column: colID(wdt, 'estdata')}
,{column: popFactCol, desc: true}]);
dView = new google.visualization.DataView(wdt);
dView.setRows(dView.getFilteredRows([
{column: colID(dView, 'sumlev'), value: '010'}
,{column: colID(dView, 'estdata'), value: popFact}
,{column: colID(dView, 'estyear'), value: popFactYear}]));

var jsonPopParams = {
"cols":
[
{"id":"","label":"Parameter(N)","pattern":"","type":"string"},
{"id":"","label":"Value","pattern":"","type":"number"}
]
,
"rows":
[
{"c":
[
{"v":"N","f":null},
{"v":0,"f":null}
]
},
{"c":
[
{"v":"Sum","f":null},
{"v":0,"f":null}
]
},
{"c":
[
{"v":"Min","f":null},
{"v":0,"f":null}
]
},
{"c":
[
{"v":"Max","f":null},
{"v":0,"f":null}
]
},
{"c":
[
{"v":"Range","f":null},
{"v":0,"f":null}
]
},
{"c":
[
{"v":"Mean","f":null},
{"v":0,"f":null}
]
},
{"c":
[
{"v":"Median","f":null},
{"v":0,"f":null}
]
},
{"c":
[
{"v":"Variance","f":null},
{"v":0,"f":null}
]
},
{"c":
[
{"v":"Standard Dev","f":null},
{"v":0,"f":null}
]
},
{"c":
[
{"v":"Skewness","f":null},
{"v":0,"f":null}
]
},
{"c":
[
{"v":"Kurtosis","f":null},
{"v":0,"f":null}
]
}]
,
"p":null
};

jsonPopParams.rows[0].c[1].f = dView.getFormattedValue(0, colID(dView, 'pn'));
jsonPopParams.rows[1].c[1].f = dView.getFormattedValue(0, colID(dView, 'psum'));
jsonPopParams.rows[2].c[1].f = dView.getFormattedValue(0, colID(dView, 'pmin'));
jsonPopParams.rows[3].c[1].f = dView.getFormattedValue(0, colID(dView, 'pmax'));
jsonPopParams.rows[4].c[1].f = dView.getFormattedValue(0, colID(dView, 'prange'));
jsonPopParams.rows[5].c[1].f = dView.getFormattedValue(0, colID(dView, 'pmean'));
jsonPopParams.rows[6].c[1].f = dView.getFormattedValue(0, colID(dView, 'pmedian'));
jsonPopParams.rows[7].c[1].f = dView.getFormattedValue(0, colID(dView, 'pvariance'));
jsonPopParams.rows[8].c[1].f = dView.getFormattedValue(0, colID(dView, 'pstndev'));
jsonPopParams.rows[9].c[1].f = dView.getFormattedValue(0, colID(dView, 'pskew'));
jsonPopParams.rows[10].c[1].f = dView.getFormattedValue(0, colID(dView, 'pkurt'));

var tblChart = new google.visualization.Table(document.getElementById(paramDiv));
wdt = new google.visualization.DataTable(jsonPopParams);
var tblView = new google.visualization.DataView(wdt);
tblChart.draw(tblView, tabOpt);


Setting a break point and stepping though each instruction, I can see that for formatted value is getting populated in the JSON object jsonPopParams.

So for example pmean has a formatted value of
'6,155,177.25'


When the chart is rendered, any value that has a comma in the formatting displays an NaN.

I created a working pared down example in JS Fiddle here:

In there I tested three scenarios


  • formatted value has no commas and 2 decimals
    ###.##
    using
    view.getFormattedValue
    and produces 6,155,177.25 this of course is the work around

  • formatted value has commas and 2 decimals
    #,###.##
    using
    view.getFormattedValue
    and produces NaN what its doing right now

  • formatted value has commas and 2 decimals but uses
    view.getValue
    and produces 6,155,177.255 the default low order positions must be 3... it works but not according to spec.



So I'm not sure if

1) I was doing it wrong before and getting away with it and now Google fixed a root cause and now my wrong code is not working any more

OR

2) Google broke something and now my correct code is not working any more.

Hopefully a second set of eyes with a fresh perspective can see though.

Any help will be appreciated. Thanks

Answer

problem appears to be these lines...

jsonPopParams.rows[0].c[1].v = dView.getFormattedValue(0, colID(dView, 'pn'));
jsonPopParams.rows[1].c[1].v = dView.getFormattedValue(0, colID(dView, 'psum'));
....  

the value property (v) is being set with the formatted value (f)

so it should either be...

jsonPopParams.rows[0].c[1].f = dView.getFormattedValue(0, colID(dView, 'pn'));

or...

jsonPopParams.rows[0].c[1].v = dView.getValue(0, colID(dView, 'pn'));

or both...

jsonPopParams.rows[0].c[1].v = dView.getValue(0, colID(dView, 'pn'));
jsonPopParams.rows[0].c[1].f = dView.getFormattedValue(0, colID(dView, 'pn'));

when the DataTable is created here...

var tblChart = new google.visualization.Table(document.getElementById(paramDiv));
wdt = new google.visualization.DataTable(jsonPopParams);
....  

strings are being used instead of numbers,
since the column type is number --> {"id":"","label":"Value","pattern":"","type":"number"}
NaN is returned