# Using Canvas to conditionally format an element powered by a query based on a previous timeframe of the same query

**URL:** <https://discuss.elastic.co/t/using-canvas-to-conditionally-format-an-element-powered-by-a-query-based-on-a-previous-timeframe-of-the-same-query/226666>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [April 6, 2020, 9:37am UTC](https://discuss.elastic.co/t/using-canvas-to-conditionally-format-an-element-powered-by-a-query-based-on-a-previous-timeframe-of-the-same-query/226666 "2020-04-06T09:37:25Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![dgbarrow](https://avatars.discourse-cdn.com/v4/letter/d/ba9def/32.png) [@dgbarrow](https://discuss.elastic.co/u/dgbarrow)\
**Post date:** [April 6, 2020, 9:37am UTC](https://discuss.elastic.co/t/using-canvas-to-conditionally-format-an-element-powered-by-a-query-based-on-a-previous-timeframe-of-the-same-query/226666/1 "2020-04-06T09:37:25Z")

</div>

Hi all,

I am currently working on a dashboard to enable me to display a traffic light system showing errors by my column "callsite". I have worked out how to conditionally format, and I have worked out how to create a second query duplicating my result set but offsetting it by 7 days historically.

What I am stuck with is conditionally formatting element A based upon query B (A - 7 Days).

my current code looks like this:

Query A

```auto
    filters

    | essql

      query="SELECT * FROM \"emma*\" where environment.keyword='UAT' and level.keyword='ERROR' and callsite.keyword = 'AgilityToEmmaUserSync.Program'"

    | math "size(message)"

    | metric "Errors triggered by AgilityToEmmaUserSync.Program"

      metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color={if {gt 0} then="red" else="black"} align="center" lHeight=48}

      labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color={if {gt 0} then="red" else="black"} align="center"}

    | render

```

Query B

```auto
    filters

    | essql

      query="SELECT * FROM \"emma*\" where environment.keyword='UAT' and level.keyword='ERROR' and callsite.keyword = 'AgilityToEmmaUserSync.Program' AND \"@timestamp\" > NOW() - INTERVAL 8 DAYS AND \"@timestamp\" < Now() - INTERVAL 7 DAYS"

    | math "size(message)"

    | metric "Errors triggered by Agility ToEmmaUserSync.Program"

      metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center" lHeight=48}

      labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"}

    | render

```

Can I somehow reference or insert Query B into the conditional formatting element of Query A? I have tried `color={if{gt (QUERYB)}}` by typing the query out again, but this is not providing the desired results.

Thank you for your time

Daniel

---

<div class="post-metadata">

**Author:** ![Catherine\_Liu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/catherine_liu/32/34294_2.png) [@Catherine\_Liu](https://discuss.elastic.co/u/Catherine_Liu)\
**Post date:** [April 6, 2020, 8:12pm UTC](https://discuss.elastic.co/t/using-canvas-to-conditionally-format-an-element-powered-by-a-query-based-on-a-previous-timeframe-of-the-same-query/226666/2 "2020-04-06T20:12:12Z")

</div>

@dgbarrow The only way to achieve this is to insert query B as part of the sub-expression for conditionally setting the color.

It would look something like this:

```auto
filters
| essql query="SELECT * FROM \"emma*\" where environment.keyword='UAT' and level.keyword='ERROR' and callsite.keyword = 'AgilityToEmmaUserSync.Program'"
| math "size(message)"
| metric "Errors triggered by AgilityToEmmaUserSync.Program"
  metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color={
    if {
      gt {
        essql query="SELECT * FROM \"emma*\" where environment.keyword='UAT' and level.keyword='ERROR' and callsite.keyword = 'AgilityToEmmaUserSync.Program' AND \"@timestamp\" > NOW() - INTERVAL 8 DAYS AND \"@timestamp\" < Now() - INTERVAL 7 DAYS" 
        | math "size(message)"
      }
    } then="red" else="black"} align="center" lHeight=48}
  labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color={
    if {
      gt {
        essql query="SELECT * FROM \"emma*\" where environment.keyword='UAT' and level.keyword='ERROR' and callsite.keyword = 'AgilityToEmmaUserSync.Program' AND \"@timestamp\" > NOW() - INTERVAL 8 DAYS AND \"@timestamp\" < Now() - INTERVAL 7 DAYS" 
        | math "size(message)"
      }
    } then="red" else="black"} align="center"}
| render

```

---

<div class="post-metadata">

**Author:** ![dgbarrow](https://avatars.discourse-cdn.com/v4/letter/d/ba9def/32.png) [@dgbarrow](https://discuss.elastic.co/u/dgbarrow)\
**Post date:** [April 7, 2020, 8:32am UTC](https://discuss.elastic.co/t/using-canvas-to-conditionally-format-an-element-powered-by-a-query-based-on-a-previous-timeframe-of-the-same-query/226666/3 "2020-04-07T08:32:56Z")

</div>

Hi Catherine,

Thank you so much for your response.

This is throwing an error -

```auto
[metric] > [font] > [if] > [gt] > [essql] > Can not cast 'number' to any of 'filter'.

```

Is this telling me that the gt function expects a type 'filter', and the query is returning a number?

Thanks

Daniel

---

<div class="post-metadata">

**Author:** ![dgbarrow](https://avatars.discourse-cdn.com/v4/letter/d/ba9def/32.png) [@dgbarrow](https://discuss.elastic.co/u/dgbarrow)\
**Post date:** [April 20, 2020, 11:18am UTC](https://discuss.elastic.co/t/using-canvas-to-conditionally-format-an-element-powered-by-a-query-based-on-a-previous-timeframe-of-the-same-query/226666/4 "2020-04-20T11:18:42Z")

</div>

Hi @Catherine_Liu, sorry to pester but could you explain this problem to me? I understand that effectively you cannot parse a query result into a mathematical equation in ESSQL the same way you can in SQL, but is there an alternative way of doing this which would enable me to achieve the desired result?

Thanks

Daniel

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [May 18, 2020, 11:18am UTC](https://discuss.elastic.co/t/using-canvas-to-conditionally-format-an-element-powered-by-a-query-based-on-a-previous-timeframe-of-the-same-query/226666/5 "2020-05-18T11:18:44Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.