Canvas - Preview shows the correct value but metric shows 0

[jas_l (Jas)]

I am on Elastic Cloud and building a metric on canvas that the Preview shows the right values but over on metric, when using -

SELECT COUNT(*) as Series_Title FROM "my_dataset" WHERE "metadataDocument_title" = 'Jack'

Preview will show the right number but when I set the Metric to display the Value of "Series_Title" it shows 0. I have tried brand new canvas and ensure no filters, time filter or any other exist.

Thoughts?
Thanks

[corey.robertson]

Hi @jas_l

Could you share the entire expression you are using for your metric element to help debug? (Select the element and click on Expression Editor in the bottom right)

[jas_l (Jas)]

Hi @corey.robertson
Here is what I have:

filters | essql query="SELECT COUNT(*) as Series_Title FROM \"demo_1*\" WHERE \"metadataDocument.asset_status\" = 'In-Market'" | math "Series_Title" | metric "value" metricFont={font family="'Open Sans', Helvetica, Arial, sans-serif" size=96 align="center" color="#FFFFFF" weight="normal" underline=false italic=false} labelFont={font family="'Open Sans', Helvetica, Arial, sans-serif" size=14 align="center" color="#FFFFFF" weight="normal" underline=false italic=false} metricFormat="0,0.[000]" | render containerStyle={containerStyle}

[corey.robertson]

Hmm, strange.

I know you said you've got all the filters removed, but I always like to step through my expressions one step at a time to help understand where I might be making a mistake.

If you change the expression to just

essql query="SELECT COUNT(*) as Series_Title FROM \"demo_1*\" WHERE \"metadataDocument.asset_status\" = 'In-Market'"

does it show the query results that match preview? From there you can start adding in each additional step of the expression. like adding on the | math "Series_Title" etc.

Let me know if that helps.

[jas_l (Jas)]

@corey.robertson Ah yes that worked. If I build the expression manually it worked. That gets me through. I think it might be although no filters, it was likely the |filters tag that was somehow overriding the result.

Thanks for your help