Chart Config - Assign Colour to Lines based on Value Name

[kai_walker-1663]

Using the Chart Config editor, can you dynamically assign a colour depending on the value name?

For example,

{Name: "Shorts", Colour: "blue"},
{Name: "Hat", Colour: "brown"}

I've done a lot of research into the chart config editor and the capabilities of it and seem to constantly hit a disappointing dead-end / end up questioning why this is a simple thing to do in Looker Studio but a painful process within Looker where you have to manually assign the colour in the values section for every chart.

The closest I have got prior to this is:

series: [{
name: 'Shorts',
color: '#b7b7b7'
},
{
name: 'Hat',
color: '#ca7fb1'
}

}]

However, this assigns the name and colour dependant on the order, meaning if I sort it the other way round it'll simply rename Hat to Shorts and visa versa.

The other attempt I made was altering the following code using the documentation provided:

{
  series: [{
    formatters: [{
      select: 'value >= 380',
      style: {
        color: 'orange'
      }
    }]
  }]
}

The select attribute
You can use the following values in a select expression:

value: This variable returns the value of the series. For example, you could use select: value > 0 to target all positive values, or value = 100 to only match series with a value of 100.
max: Use select: max to target the series value that has the maximum value.
min: Use select: min to target the series value that has the minimum value.
percent_rank: This variable targets the series value with a specified percentile. For example, you could use select: percent_rank >= 0.9 to target series values in the ninetieth percentile.
name: This variable returns the dimension value of the series. For example, if you had a chart showing Sold, Canceled, and Returned orders, you could use select: name = Sold to target the series where the dimension value is Sold.

However, the suggestion about using name doesn't seem to work / I am unable to get it to work. 

I'd really appreciate any help on this subject or simply some closure so I can move on and know it's simply a missing functionality and not my own error.

Thanks in advance!

[finn_google]

Hi @kai_walker-1663, thanks for the question. Something like the below snippet should work to format a value based on its name, independent of sort order:

{
  chart: {},
  series: [{
    formatters: [{
        select: 'name = Jeans',
        style: {
          color: 'orange'
        }
      },
      {
        select: 'name = Accessories',
        style: {
          color: 'red'
        }
      }
    ],
    name: 'Order Count'
  }]
}

[kai_walker-1663]

Hey! I appreciate the speedy response. After looking into this and trying this method on my time series chart where the dimension is pivoted so I can view each value as a separate line I was able to understand why this method doesn't work.

It looks like when you pivot by the column it can no longer be referenced by 'name = abc'.

Is there a solution to this or can you only colour specific values for bar charts and not line charts?

[finn_google]

Hi @kai_walker-1663, upon further digging into this, I found a way to apply formatting to pivoted columns:

First to clear up when conditional data formatting is best used - in the non-pivoted example above, the "series" of data is the Order Count, and within that you have a column named Accessories. For that you would need to use the conditional data formatting.

Once you pivot (for example, by product category), every column becomes its own series. So in this case you should target the series named Accessories directly without using the conditional data formatting, as in the example below. The best practice in the Advanced Chart Config here is that once you make a structural change to a visualisation by (e.g.) pivoting, you can Clear Chart Overrides and work from the new viz config 

Screenshot in case the GIF is too low-res:

And an example of it working for line charts:

 

[kai_walker-1663]

Hi Finn,

Thanks so much for continuing to look into this and working toward a solution!

Unfortunately, I encountered an issue when I tried this approach before. After assigning that colour, if you filter out one of the pivot values from the results, you’ll notice that the colour and name get passed down to the next item in order. For example, in the scenario you gave, removing "Jeans" causes "Shorts" to appear as "Jeans" as well as the colour given for Jeans

Let me know if you have any thoughts on this!

[finn_google]

Hi @kai_walker-1663, ah yes, I see what you mean - I think the best way to apply consistent formatting to pivoted columns at the moment is in the "Edit - Series" panel (GIF below), where you can assign a colour to a pivoted column that continues to work as expected when the query changes.

Screenshot for reference:

There is an open feature request to be able to use the Advanced Chart Config to format pivoted columns based on field name rather than the pivoted column sort order.