Highcharts language configuration: thousandsSep

Question & Answer
, ,
1

Hi,

The newly added feature for incorporating Highcharts visuals in QuickSight is fantastic! I believe it’s one of the most valuable updates introduced in recent months.

I’ve been exploring it and tried using examples from Demo Central as well as adapting code from the Highcharts website to work in QuickSight. However, I’m encountering some challenges.

I understand that Highcharts in QuickSight only supports valid JSON, which means functions aren’t currently supported.

For instance, in Highcharts, we can use functions like the one below to set decimal points and thousand separators:
https://api.highcharts.com/highcharts/lang.thousandsSep

$(function () {
Highcharts.setOptions({

    lang: {
      decimalPoint: '.',
      thousandsSep: ','
    }
});

How to add this to this example code?

{
  "chart": {
    "type": "variwide"
  },
  "xAxis": {
    "type": "category"
  },
  "caption": {
    "text": "Column widths are proportional to Profit"
  },
  "legend": {
    "enabled": false
  },
  "series": [
    {
      "type": "bullet",
      "name": "Products",
      "data": [
        "map",
        ["getColumn", 0, 1, 2], // Product, Sales, Profit
        {
          "name": ["get", ["item"], 0], // Product
          "y": ["get", ["item"], 1], // Sales
          "z": ["get", ["item"], 2] // Profit
        }
      ],
      "tooltip": {
        "useHTML": true,
        "headerFormat": "<span style=\"color:{point.color}\">\u25CF</span>",
        "pointFormat": "<b>{point.name}</b><br/>Sales: <b>{point.y:,.2f}</b><br/>Profit: <b>{point.z:,.2f}</b><br/>"
      },
      "dataLabels": {
        "enabled": true,
        "format": "{point.y:,.2f}",
        "style": {
          "textOutline": "none",
          "fontWeight": "normal",
          "fontSize": "12px"
        }
      },
      "borderRadius": 3,
      "colorByPoint": true
    }
  ]
}
1 Like
2

You’re right, the lack of support for functions in the Highcharts JSON configuration within Amazon QuickSight can be a bit limiting. However, there are a few workarounds you can try to achieve the desired formatting for decimal points and thousand separators.

  1. Modify the Data Source:
    Instead of applying the formatting in the Highcharts configuration, you can modify the data source to include the formatted values. For example, you can create a calculated field in your QuickSight dataset that formats the values with the desired decimal and thousand separators, and then use that field in your Highcharts configuration.
CONCAT(
  FORMAT(CAST({Profit} AS DECIMAL(18,2)), '#,##0.00'),
  ' ',
  {Product}
) AS formattedData

This will create a new field formattedData that includes the formatted Profit value and the Product name. You can then use this field in your Highcharts configuration instead of the raw Profit value.
2. Use Custom HTML/CSS Formatting:
Another approach is to use custom HTML and CSS formatting within the Highcharts configuration to achieve the desired formatting. You can leverage the useHTML option in the tooltip and dataLabels configurations to apply custom HTML and CSS.

"tooltip": {
  "useHTML": true,
  "headerFormat": "<span style=\"color:{point.color}\">\u25CF</span>",
  "pointFormat": "<b>{point.name}</b><br/>Sales: <b>DISCOURSE_PLACEHOLDER_7nbsp;{point.y:,.2f}</b><br/>Profit: <b>DISCOURSE_PLACEHOLDER_7nbsp;{point.z:,.2f}</b><br/>"
},
"dataLabels": {
  "enabled": true,
  "format": "<span style=\"font-size: 12px; font-weight: normal; color: {point.color};\">DISCOURSE_PLACEHOLDER_7nbsp;{point.y:,.2f}</span>",
  "style": {
    "textOutline": "none"
  }
}
  1. In this example, we’ve added the DISCOURSE_PLACEHOLDER_8nbsp; prefix to the formatted values in the tooltip and data labels to create a space between the currency symbol and the number.
  2. Use a Custom Highcharts Theme:
    If you need to apply the formatting across multiple Highcharts visualizations, you can consider creating a custom Highcharts theme and applying it to your configurations. This way, you can define the formatting options, such as the decimal point and thousand separators, in a centralized location.To create a custom theme, you can refer to the Highcharts documentation on creating and applying themes.

Keep in mind that while these workarounds can help you achieve the desired formatting, the lack of support for functions in the Highcharts JSON configuration within Amazon QuickSight may still limit your ability to implement more complex formatting logic.

3

Thank you @murili

I don’t think option 1 “modify the data source” is something suitable, because we are doing many aggregations and calculations on QuickSight itself.

Option 2 is just for formatting the text, which can not be used to format numbers.

Using a custom theme is not doable also, because to create a theme, we have to apply it like this:

Highcharts.setOptions(Highcharts.theme);

I think QuickSight team did not include support for JavaScript functions to avoid risk of breaking things and misusing it, but what they can do is to add more boxes to set theme and options like this:

image
image969×431 10.7 KB

for setOptions they can keep it as a code box, or they can just add click to add boxes with list of accepted functions, variables, setters…

2 Likes
4

You can use QuickSight formatting in Highcharts visuals. Configure the decimal point and thousands separator on the field level, and then use "formatter": ["formatValue", "value", 1] expression - this gets the formatter of the second field well. Here is a full example:

{
  "xAxis": {
    "categories": ["getColumn", 0]
  },
  "yAxis": {
    "labels": {
      "formatter": ["formatValue", "value", 1]
    }
  },
  "series": [
    {
      "type": "line",
      "data": ["getColumn", 1]
    }
  ]
}

Results:

image
image1551×700 80.4 KB

5

I’m using this already for both x any y axis, but unfortunately, this will not work with the tooltip.

image
image182×142 9.98 KB

1 Like