Google charts: cannot format numeric data with commas

Viewed 810

[Edit: problem solved, see solution at end, which shows the code implementing WhiteHat's accepted, working answer. I've left the original example non-working code here because it does implement two different accepted solutions that don't work in this case. My hope is that the before and after examples will be of use to others who may stumble down this path]

I cannot get Google Chart's NumberFormat() to insert commas to group by thousands and/or to add a currency symbol. The same formatting pattern I'm using for my vertical axis works correctly.

I've tried two accepted solutions (here) and (here) with no luck. The first accepted solution uses a pattern in the number formatter, the second uses a grouping symbol.

Here is a snapshot of how my chart looks (green and red markup added to illustrate problem):

enter image description here

What's odd is I can use the pattern correctly to format the vertical axis (see the green checkmarks), but the same thing won't work with a numberformatter (the red checkmarks).

Here is my code for the numberformatter, which, as I understand, SHOULD give me the comma separated values in the body of my chart:

        var formatter = new GoogleCharts.api.visualization.NumberFormat({
        negativeColor: 'red',
        negativeParens: true,
        pattern: currencySymbol + '###,###',
        });

    formatter.format(data, 1);

currencySymbol is just a string variable holding, in this case, the symbol for the Mexican Peso. In case it was causing the problem though, I did try removing it and just using the following formatting:

        var formatter = new GoogleCharts.api.visualization.NumberFormat({
        negativeColor: 'red',
        negativeParens: true,
        pattern:'###,###',
        });

    formatter.format(data, 1);

Same result though, no commas in the body of my chart.

What's confusing is that, if you look at my vertical axis formatting, the very same pattern, specified in my chart options, gives me exactly the results I want on the vertical axis.

            vAxis: {
           format: currencySymbol + '###,###',
            maxValue: 350,   
        },

As mentioned, I also tried an alternate accepted Stack Overflow answer, where I specified a grouping symbol, like this, but it also does not work:

        var formatter = new GoogleCharts.api.visualization.NumberFormat({
        negativeColor: 'red',
        negativeParens: true,
        prefix: currencySymbol,
        groupingSymbol: ',',
        });

    formatter.format(data, 1);

[EDIT: WhiteHat, in his accepted answer, has solved the problem. Below is the working code. In hopes of helping the community, I've left the non-working example code in place above as others may go down the same path (that path being some of the accepted StackOverflow accepted answers that don't work in this case)]

Here is my refactored code that builds the data and annotations:

            let dataToBind = [['Year', 'Revenue', { role: 'style' }, { role: 'annotation' }]];

        let currencySymbol: string = convertCurrencyToSymbol(this.data.currencyCode);

        var formatter = new GoogleCharts.api.visualization.NumberFormat({
            negativeColor: 'red',
            negativeParens: true,
            pattern: currencySymbol + '###,###',
        });

        let mappedData: any = this.data.chartData.map(d => [d.year, d.revenue, `opacity: 0.5; fill-color:${this.getNextColor()}`, `${**formatter.formatValue(d.revenue)**}`]);  

        dataToBind.push(...mappedData);

        let data = GoogleCharts.api.visualization.arrayToDataTable(dataToBind);

As WhiteHat points out, applying formatter.formatValue() on EACH annotation is the way to get around this problem. As mentioned previously, the currencySymbol variable is just a simple string variable I load from a local function so that I can get the correct value for Pesos, Euros, etc.

Here is what my chart looks like after applying this change. This is the desired effect, with commas and currency symbols:

enter image description here

1 Answers

the formatter has two methods...

format will format a data table column
formatValue will format a single value

try using formatValue when loading your data.

formatter.formatValue(d.revenue)

e.g.

let mappedData: any = this.data.chartData.map(d => [d.year, d.revenue, `opacity: 0.5; fill-color:${this.getNextColor()}`, formatter.formatValue(d.revenue)]);  // ${currencySymbol}
Related