# Failed visualize data from Elastic SQL query in Canvas Bar Chart **URL:** \ **Category:** Kibana\ **Tags:** elastic-stack-sql, canvas\ **Created:** [May 29, 2019, 12:20pm UTC](https://discuss.elastic.co/t/failed-visualize-data-from-elastic-sql-query-in-canvas-bar-chart/183327 "2019-05-29T12:20:22Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![ofitz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ofitz/32/47071_2.png) [@ofitz](https://discuss.elastic.co/u/ofitz)\ **Post date:** [May 29, 2019, 12:20pm UTC](https://discuss.elastic.co/t/failed-visualize-data-from-elastic-sql-query-in-canvas-bar-chart/183327/1 "2019-05-29T12:20:22Z")
I create a canvas board and would like to visualize data in "realtime". My board: ![image](https://us1.discourse-cdn.com/elastic/original/3X/1/c/1c64a4fc5d9d473bd991f28756712ffc1c484579.png) My Datasource SQL query: SELECT TIMESTAMP, SENSOR, LAST(VALUE) VALUE FROM "runtime\_kibana\_stream" WHERE SENSOR='knife01' OR SENSOR='knife02' OR SENSOR='knife03' GROUP BY TIMESTAMP, SENSOR ORDER BY TIMESTAMP DESC Datasource preview ist correct: ![image](https://us1.discourse-cdn.com/elastic/original/3X/3/f/3fc8e86b2268a8a1cc7c732002f68c72e56f1095.png) But my bar chart show me not the LAST value of the data. Idea where ist the Problem? thx Otto ---
**Author:** ![Brandon\_Kobel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/brandon_kobel/32/14829_2.png) [@Brandon\_Kobel](https://discuss.elastic.co/u/Brandon_Kobel)\ **Post date:** [May 29, 2019, 4:46pm UTC](https://discuss.elastic.co/t/failed-visualize-data-from-elastic-sql-query-in-canvas-bar-chart/183327/2 "2019-05-29T16:46:57Z")
Hey @ofitz, what do you have selected for the Y-axis? Using the following shows me the expected data: ![43%20AM](https://us1.discourse-cdn.com/elastic/original/3X/5/c/5c587bace9b2a7b3f7c7db91a03f2c3ae5388460.png) ---
**Author:** ![ofitz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ofitz/32/47071_2.png) [@ofitz](https://discuss.elastic.co/u/ofitz)\ **Post date:** [May 29, 2019, 8:10pm UTC](https://discuss.elastic.co/t/failed-visualize-data-from-elastic-sql-query-in-canvas-bar-chart/183327/3 "2019-05-29T20:10:00Z")
Hi Brandon, if you get the FIRST value on Y-axis and add new Value to knife01 with new timestamp which is smaller then 5, the bar chart dont visualized it. The bar chart stays on 5 @ knife01 ☹ ---
**Author:** ![Brandon\_Kobel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/brandon_kobel/32/14829_2.png) [@Brandon\_Kobel](https://discuss.elastic.co/u/Brandon_Kobel)\ **Post date:** [May 29, 2019, 8:22pm UTC](https://discuss.elastic.co/t/failed-visualize-data-from-elastic-sql-query-in-canvas-bar-chart/183327/4 "2019-05-29T20:22:21Z")
Would you mind providing a screen-shot of what you're seeing because I'm afraid I'm misunderstanding what you're describing. The `ORDER BY TIMESTAMP DESC` will always make the most recent value for "knife01" show up. ---
**Author:** ![ofitz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ofitz/32/47071_2.png) [@ofitz](https://discuss.elastic.co/u/ofitz)\ **Post date:** [May 30, 2019, 10:28am UTC](https://discuss.elastic.co/t/failed-visualize-data-from-elastic-sql-query-in-canvas-bar-chart/183327/5 "2019-05-30T10:28:25Z")
thx Brandon, to get the last value failed on stream processing on kafka after reboot the server ist works, and with the FIRST value on y-axis i get the actually value 🙂 The next problem is with the x-axis which i get. The name of the sensors have no static position on the x-axis. in this Video you can see that. In the middle of the video you can see the sensor names and bars are change the position. I would like static postion on x-axis and variable y-axis > **[discuss.elastic - Kibana - Google Chrome 30.05.2019 12\_17\_52.mp4](https://cloud.dienes.de/index.php/s/W8o9FgGKmnyrCbi)** > > DIENES Cloud - DIENES IT Webservices Any idea? By the way, can i change each bar chart color? or change the color of one bar is over of a specific value? ---
**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:** [May 31, 2019, 9:19pm UTC](https://discuss.elastic.co/t/failed-visualize-data-from-elastic-sql-query-in-canvas-bar-chart/183327/6 "2019-05-31T21:19:37Z")
> [@ofitz](#): > > SELECT TIMESTAMP, SENSOR, LAST(VALUE) VALUE > FROM "runtime\_kibana\_stream" > WHERE SENSOR='knife01' OR SENSOR='knife02' OR SENSOR='knife03' > GROUP BY TIMESTAMP, SENSOR > ORDER BY TIMESTAMP DESC Try this expression and see if it produces the correct results you're looking for: ```auto filters | essql query="SELECT TIMESTAMP, SENSOR, LAST(VALUE) VALUE FROM \"runtime_kibana_stream\" WHERE SENSOR='knife01' OR SENSOR='knife02' OR SENSOR='knife03' GROUP BY TIMESTAMP, SENSOR ORDER BY TIMESTAMP DESC" | ply by=SENSOR fn={math "first(VALUE)" | as "VALUE"} | pointseries x=SENSOR y=VALUE | plot | render ``` What the [`ply`](https://www.elastic.co/guide/en/kibana/current/canvas-common-functions.html#_ply) function is doing here is it's grouping your `datatable` by `SENSOR` and applying the `fn` subexpression to each group. Here the `fn` is using TinyMath to grab the first value in the `VALUE` column, which grabs the newest row since your datasource is sorted in descending order by `TIMESTAMP`. It should update with date as new documents come in. Side note: I don't think the `GROUP BY TIMESTAMP, SENSOR` is necessary in your ESSQL query unless you expect to have multiple documents for the same exact timestamp per sensor. ---
**Author:** ![ofitz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ofitz/32/47071_2.png) [@ofitz](https://discuss.elastic.co/u/ofitz)\ **Post date:** [June 2, 2019, 4:01pm UTC](https://discuss.elastic.co/t/failed-visualize-data-from-elastic-sql-query-in-canvas-bar-chart/183327/7 "2019-06-02T16:01:32Z")
Hi Catherine\_Liu, i tried your expression, but the position on the x.axis continues change. in this screenshot you can see my streamingdata to ES: ![image](https://us1.discourse-cdn.com/elastic/original/3X/f/f/ffc2182f16b76d5aeba297f03c2c3302e904695e.png) my expression filter are: ![image](https://us1.discourse-cdn.com/elastic/original/3X/f/7/f71ebecafacfb8de8339b5c1ab38c6b1b84129c8.png) I need the expression `GROUP BY TIMESTAMP, SENSOR` without this expression i get this: ![image](https://us1.discourse-cdn.com/elastic/original/3X/9/a/9ae3953c3439fa44750e892f385051fa4466160b.png) ---
**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:** [June 28, 2019, 9:55pm UTC](https://discuss.elastic.co/t/failed-visualize-data-from-elastic-sql-query-in-canvas-bar-chart/183327/8 "2019-06-28T21:55:46Z")
> [@Catherine\_Liu](#): > > filters | essql query="SELECT TIMESTAMP, SENSOR, LAST(VALUE) VALUE FROM "runtime\_kibana\_stream" WHERE SENSOR='knife01' OR SENSOR='knife02' OR SENSOR='knife03' GROUP BY TIMESTAMP, SENSOR ORDER BY TIMESTAMP DESC" | ply by=SENSOR fn={math "first(VALUE)" | as "VALUE"} | pointseries x=SENSOR y=VALUE | plot | render To maintain the same order of values in the x-axis, try adding a `sort` function in between `ply` and `pointseries` to sort the `SENSOR` field in asc order. ```auto filters | essql query="SELECT TIMESTAMP, SENSOR, LAST(VALUE) VALUE FROM \"runtime_kibana_stream\" WHERE SENSOR='knife01' OR SENSOR='knife02' OR SENSOR='knife03' GROUP BY TIMESTAMP, SENSOR ORDER BY TIMESTAMP DESC" | ply by=SENSOR fn={math "first(VALUE)" | as "VALUE"} | sort by=SENSOR | pointseries x=SENSOR y=VALUE | plot | render ``` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 26, 2019, 9:55pm UTC](https://discuss.elastic.co/t/failed-visualize-data-from-elastic-sql-query-in-canvas-bar-chart/183327/9 "2019-07-26T21:55:47Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.