[Looker] [Chart Editor] Referencing fields in Chart Editor config

[Dawid]

Has anybody been able to figure out how to reference other fields from the data table in the Chart Editor? 

I tried to test it with: 

{
  chart: {},
  series: [{
    name: 'Override Series Name',
    dataLabels: {
      color: 'red',
      format: '${invoices.value}'
  }]
}

I even used the field that's already in the chart used for yAxis value - yes that is the default behaviour but I wanted to know whether it's accessing different field or the configuration. It seems like even using the same field doesn't work, hence I wonder if we're even allowed to reference fields? Google documentation on Chart Editor is tiny and referring us to Highcharts documentation is kind of pointless without knowing what the limitations of Chart Editor are.

[sam8]

Hey Dawid!

You can access some templating values, but it's all related to the point itself. I don't think Highcharts itself provides a syntax for referencing other points.

Do you know if you can achieve your goal with Highcharts itself outside of Looker?

[Dawid]

Yes it's possible in Highcharts to refer to other fields, this is from their gpt docs:

Yes, you can use different fields from your dataset to customize the data labels in Highcharts. You can achieve this by using the dataLabels property within the series configuration. You can format the labels using the formatter function to access different fields from your data.

Here's an example configuration:

Highcharts.chart('container', {
    title: {
        text: 'Custom Data Labels Example'
    },
    series: [{
        name: 'Sample Series',
        data: [
            { x: Date.UTC(2020, 0, 1), y: 29.9, customField: 'A' },
            { x: Date.UTC(2020, 1, 1), y: 71.5, customField: 'B' },
            { x: Date.UTC(2020, 2, 1), y: 106.4, customField: 'C' }
        ],
        dataLabels: {
            enabled: true,
            formatter: function() {
                return 'Value: ' + this.y + ', Custom: ' + this.point.customField;
            }
        }
    }]
});

I'm the end I'm doing all sorts of gymnastic here tol find how how can I refer to other fields in the data table in the chart config 

[sam8]

I see. Yeah, I don't think this is possible using the tools we have available in the Chart Config Editor today.

The "formatter" attribute, while powerful, is not supported in Looker. I double checked with our engineers and we weren't able to find a workaround. I recommend creating a feature request!

[Dawid]

@sam8 the problem is that the documentation in Google doesn't tell us what we can and can't do with Highcharts. You can't say "here is Highcharts documentation" without saying what's allowed or not. I wanted to write couple of articles about different use cases and one of them was reading colours of series from different fields and I spent hours trying to achieve it. Something that is simple in Highcarts.. 

We need to know what's supported and what is not, otherwise it's like dangling a great thing in front of you that you can just smell but can't reach

[sam8]

Hey Dawid, that is a totally fair point. 

The main rule of thumb you can use is: Looker supports most static JSON configurations, but not dynamic calculations (such as Javascript functions). That's why formatter isn't allowed. I can add this note to the docs if you think it'd be helpful!

We don't have any plans to post a list of the unsupported static properties - this changes each release and most of the changes are minor. My guess is that most of the use cases you are struggling to replicate in Looker require dynamic calculations, but please let me know if that's an incorrect assumption.

[Dawid]

The main use case is that I want to use different dimension in the colours, so not even JavaScript or dynamic. 

Though now that I asked Highcharts for an example, it does require usage of JS map function:

// Sample data including revenue and color for each month
const data = [
    { month: 'January', revenue: 3000, color: '#FF5733' },
    { month: 'February', revenue: 4000, color: '#33FF57' },
    { month: 'March', revenue: 3500, color: '#3357FF' },
    { month: 'April', revenue: 4500, color: '#F1C40F' },
    { month: 'May', revenue: 5000, color: '#8E44AD' },
    { month: 'June', revenue: 6000, color: '#E67E22' },
    { month: 'July', revenue: 7000, color: '#2ECC71' },
    { month: 'August', revenue: 8000, color: '#3498DB' },
    { month: 'September', revenue: 7500, color: '#9B59B6' },
    { month: 'October', revenue: 8500, color: '#F39C12' },
    { month: 'November', revenue: 9000, color: '#D35400' },
    { month: 'December', revenue: 10000, color: '#C0392B' }
];

// Prepare the series data
const seriesData = data.map(item => ({
    y: item.revenue,
    color: item.color
}));

Highcharts.chart('container', {
    chart: {
        type: 'column'
    },
    title: {
        text: 'Monthly Revenue for 2023'
    },
    xAxis: {
        categories: data.map(item => item.month)
    },
    yAxis: {
        title: {
            text: 'Revenue'
        }
    },
    series: [{
        name: 'Revenue',
        data: seriesData
    }]
});

But in the past I managed to get Highcharts gpt to show me how different fields are references, or maybe it was even Gemini when I asked about Looker specifically. 

Just FYI, allowing some of these better customisations is where real power lies. 

What functionalities are supported? Apart from these few ones mentioned in the docs?

[sam8]

The static vs dynamic rule helps here too. Referencing another dimension value color code would require using functions, as you mentioned with `data.map`. 

When it comes to changing anything static, almost everything is fair game. You can change the background color, label and legend color/style, and tooltip color/style. You can change the weight of a line graph to dots or dashes. You can add a couple things that weren't available in Looker in the first place, such as chart annotations and vertical reference bands. You can also now conditionally format chart values outside of just table visualizations. 

I'm trying to keep the docs updated with specific examples of the supported changes that solve our most long-standing feature requests around visualizations. Even without using dynamic functions, there's still a lot that this feature unlocks.