How to reference another column value in metricFont expression?

Hi,
I have a table that returns only one row with two columns: carrier and sum_orders.

I'm currently displaying sum_orders in a metric element. I would like to change the font color of the number based on the value in the carrier column, which can be one of the following: 1, 2, 3, 4, or 5.

How can I reference the carrier value in the expression?

Here is the query I’m using:

kibana
| selectFilter
| essql query="
  SELECT
    case
        when delivery_string like '%Transp%' then 4
        when delivery_string like '%DH%' then 5
        when delivery_string like '%UP%' then 3
        when delivery_string like '%CO%' then 2
        when delivery_string like '%RE%' then 1
        else 4
    end as carrier,
    sum(qty_ordered) as sum_ordered

  FROM \"ehs-outbound\"
  WHERE 1 = 1
    AND order_type = 'DL1'
  GROUP BY 1
  ORDER BY 2
  LIMIT 1
"

| math "sum_ordered"
| metric "sum_ordered"

  metricFont={font align="center" color={switch {case if={eq 1} then="#fd986f"} {case if={eq 2} then="#f5cc5d"} {case if={eq 3} then="#ff0000"} {case if={eq 4} then="#ff8000"} {case if={eq 5} then="#808000"} default="#000000"} family="'Open Sans', Helvetica, Arial, sans-serif" italic=false size=96 underline=false weight="normal"} 

  labelFont={font size=1 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"} metricFormat="0,0.[000]"

| render

The switch statement in the metricFont currently references the sum_orders value. How can I make it reference the carrier value instead?

Thanks in advance!

No one knows?

Here’s a simplified version:

kibana
| selectFilter
| essql query="   
  SELECT
    SUM(0) + 2 AS color_index
    SUM(0) + 400 AS sum_of_orders
  FROM \"ehs-outbound\"
"
| math "sum_of_orders"
| metric "sum_of_orders"
    
metricFont={font align="center" color={switch {case if={eq 1} then="#fd986f"} {case if={eq 2} then="#f5cc5d"} default="#000000"} family="'Open Sans', Helvetica, Arial, sans-serif" italic=false size=96 underline=false weight="normal"}     

labelFont={font size=1 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"} metricFormat="0,0.[000]"  | render

The essql query creates two columns and one row, color_index and sum_of_orders. The switch statement refers to the sum_of_orders column.

color={switch {case if={eq 1} then="#fd986f"} {case if={eq 2} then="#f5cc5d"} default="#000000"}

In other words, if sum_of_orders is 1 then font color will be #fd986f.

I want it to refer to color_index instead. Is that possible?

If color_index = 1 then color = #fd986f.

/Jens

Is it true that once I write:

| math "sum_of_orders"

all the remaining code refers only to that variable?
Does that mean I can no longer access the table created by the essql query?

Is it possible to use sum_of_orders as the number displayed in the metric, and then use color_index to determine its font color?

Wow, this forum is incredibly slow… Doesn’t anyone know the answer to this? The question has 31 views, and I thought Elastic developers were active here?

It’s not the first time I’ve posted something and had to wait a long time — or received no answer at all.

Is there a better way to get help? I also tried asking on Stack Overflow but got nothing there either.

If someone could at least point me in the right direction, I’d really appreciate it. Where should I go with questions like this? Does Elastic have an official support channel?

You can also try on Slack but I can not tell if there are more Kibana devs there than here.

Does Elastic have an official support channel?

Yes. Have a look at Elastic Support & Customer Service | Elastic