Changing number format in table

Sylwia

unread,

Nov 14, 2013, 10:57:45 AM11/14/13

to google-visua...@googlegroups.com

I am using the SuperProxy API and Google Charts. I have a table with multiple columns and the numbers in each column should have a different format.

At the moment the formats are:

- Column 2: 46.354352

- Column 5: 7.34523542

- Column 6: 641.4352

While I want:

- Column 2: 46.35% (percentage)

- Column 5: 7.35 (Only 2 decimal points)

- Column 6: 10:41 (mm:ss)

I tried changing the format of one of the columns with the code below but nothing happens. What would be the best way to change the number format?

Thanks

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<head>

<title>

Neon Dashboard

</title>

src='https://www.google.com/jsapi?autoload={"modules":[{"name":"visualization","version":"1"}]}'>

</script>

google.setOnLoadCallback(drawVisualization);

function drawVisualization() {

var querd = new google.visualization.Query('https://my.appspot.com/query?id=gdrgssegyrthyjuyjg&format=data-table-response');

querd.send(function (response) {

var datad = response.getDataTable();

datad.setColumnLabel(0, 'Visits');

datad.setColumnLabel(1, 'Unique Visitors');

datad.setColumnLabel(2, 'Bounce Rate');

datad.setColumnLabel(3, 'Pageviews');

datad.setColumnLabel(4, 'Pages/Visit');

var formatter = new google.visualization.NumberFormat({

'fractionDigits': '2',

'pattern':'#%'

});

formatter.format(datad, 2);

var Wrapperrd = new google.visualization.ChartWrapper({

'containerId': 'd2dtable',

'dataTable': datad,

'chartType': 'Table',

'options': {'width': '630px'},

'view': {'column': [{'format': '#\'%\''}, 2]

}

});

Wrapperrd.draw();

});

}

</script>

</head>

<body>

<tr>

</td>

</tr>

</body>

</html>

asgallant

unread,

Nov 14, 2013, 11:43:45 AM11/14/13

to google-visua...@googlegroups.com

Using the "pattern" parameter of the NumberFormatter overrides all other options. Here's how you would format your 3 series:

var percentFormatter = new google.visualization.NumberFormat({pattern: '#.00\'%'}); // use ' to escape the % so it doesn't multiply by 100
percentFormatter.format(data, 2);
var numberFormatter = new google.visualization.NumberFormat({pattern: '#.00'});
percentFormatter.format(data, 5);

// custom format column 6 to mm:ss
for (var i = 0, length = datad.getNumberOfRows(), value, minutes, seconds; i < length; i++) {
    value = datad.getValue(i, 6);
    minutes = Math.floor(value / 60);
    seconds = Math.round(value % 60);
    // prepend 0's if necessary
    minutes = (minutes < 10) ? '0' + minutes : minutes;
    seconds = (seconds < 10) ? '0' + seconds : seconds;
    datad.setFormattedValue(i, 6, minutes + ':' + seconds);
}

Sylwia

unread,

Nov 15, 2013, 6:55:24 AM11/15/13

to google-visua...@googlegroups.com

Thanks, the number formatting and time formatting worked perfectly but the percent formatting doesn't. Any idea why this could be? See the code below and the data types

{"kind": "analytics#gaData", "rows": [["9340", "602", "46.85224839400428", "70290", "7.525695931477516", "649.1922912205567"]], "containsSampledData": false, "totalsForAllResults": {"ga:visitBounceRate": "46.85224839400428", "ga:avgTimeOnSite": "649.1922912205567", "ga:visits": "9340", "ga:visitors": "602", "ga:pageviewsPerVisit": "7.525695931477516", "ga:pageviews": "70290"}, "itemsPerPage": 50, "query": {"max-results": 50, "start-date": "2013-10-16", "start-index": 1, "segment": "gaid::URF4k_fqRlCtmYJcNu_NJg", "metrics": ["ga:visits", "ga:visitors", "ga:visitBounceRate", "ga:pageviews", "ga:pageviewsPerVisit", "ga:avgTimeOnSite"], "end-date": "2013-11-15"}, "totalResults": 1, "columnHeaders": [{"dataType": "INTEGER", "columnType": "METRIC", "name": "ga:visits"}, {"dataType": "INTEGER", "columnType": "METRIC", "name": "ga:visitors"}, {"dataType": "PERCENT", "columnType": "METRIC", "name": "ga:visitBounceRate"}, {"dataType": "INTEGER", "columnType": "METRIC", "name": "ga:pageviews"}, {"dataType": "FLOAT", "columnType": "METRIC", "name": "ga:pageviewsPerVisit"}, {"dataType": "TIME", "columnType": "METRIC", "name": "ga:avgTimeOnSite"}]

var quer = new google.visualization.Query('https://app.appspot.com/query?id=avregeshbgrteFCgw&format=data-table-response');

quer.send(function (response) {

var datat = response.getDataTable();

datat.setColumnLabel(0, 'Visits');

datat.setColumnLabel(1, 'Unique Visitors');

datat.setColumnLabel(2, 'Bounce Rate');

datat.setColumnLabel(3, 'Pageviews');

datat.setColumnLabel(4, 'Pages/Visit');

datat.setColumnLabel(5, 'Avg. Time on Site');

var percentFormatter = new google.visualization.NumberFormat({'pattern': '#.00\'%'});

percentFormatter.format(datat, 2);

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

numberFormatter.format(datat, 4);

for (var i = 0, length = datat.getNumberOfRows(), value, minutes, seconds; i < length; i++) {

value = datat.getValue(i, 5);

minutes = Math.floor(value / 60);

seconds = Math.round(value % 60);

datat.setFormattedValue(i, 5, minutes + ':' + seconds);

}

var Wrapperrr = new google.visualization.ChartWrapper({

'containerId': 'iprospectable',

'dataTable': datat,

'chartType': 'Table',

'options': {'width': '630px'}

});

Wrapperrr.draw();

});

asgallant

unread,

Nov 15, 2013, 11:10:19 AM11/15/13

to google-visua...@googlegroups.com

The percent formatter works when I use it: http://jsfiddle.net/asgallant/4GyAL/. What does it output when you use it?

Sylwia

unread,

Nov 15, 2013, 11:34:47 AM11/15/13

to google-visua...@googlegroups.com

That's really strange... It just returns the not formatted number: 46.85224839400428

I also just noticed that for one of the cells the time formatter transforms 728.8043250784792 into 12:9

asgallant

unread,

Nov 15, 2013, 12:08:15 PM11/15/13

to google-visua...@googlegroups.com

Check your DataTable to make sure the data type of the percent column is "number" and not "string". NumberFormatters have no effect on string columns.

The time format as written should format 728.8043250784792 as "12:09", as the 8.8 seconds rounds up to 9, 9 < 10, so a leading 0 should be prepended. I don't know how it could output "12:9".

Sylwia

unread,

Nov 18, 2013, 5:00:37 AM11/18/13

to google-visua...@googlegroups.com

The percent column is in fact a string. Is there I way I could convert it into a number?

Thanks

Sylwia

unread,

Nov 18, 2013, 6:39:50 AM11/18/13

to google-visua...@googlegroups.com

Ignore the question. I figured it out.

for (var c=0, length = datad.getNumberOfRows(), myVal; c < length; c++) {

myVal = parseFloat(datad.getValue(c, 2)).toFixed(2);

datad.setFormattedValue(c, 2, myVal + '%');

}

Thanks for all the help!

asgallant

unread,

Nov 18, 2013, 10:37:56 AM11/18/13

to google-visua...@googlegroups.com

You might want to consider converting ther "percent" column type to "number" instead of "string", as the API will treat numbers and strings differently in most visualizations, and you can do things with number columns that you cannot do with string columns.

asgallant

unread,

Nov 18, 2013, 10:41:09 AM11/18/13

to google-visua...@googlegroups.com

As an example: consider the case of sorting your data. If your percent column has the values 5%, 15%, and 30%, sorting the column will have different results for string and number types. A string type column will sort them in the order "15%", "30%", "4%" (because "1" < "3" < "4"), while a number column will sort them 4%, 15%, 30%.

Reply all

Reply to author

Forward