# How to create a new line on a kibana canvas table **URL:** \ **Category:** Kibana\ **Tags:** canvas\ **Created:** [September 10, 2020, 1:38pm UTC](https://discuss.elastic.co/t/how-to-create-a-new-line-on-a-kibana-canvas-table/248179 "2020-09-10T13:38:09Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![francieliton\_araujo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/francieliton_araujo/32/47771_2.png) [@francieliton\_araujo](https://discuss.elastic.co/u/francieliton_araujo)\ **Post date:** [September 10, 2020, 1:38pm UTC](https://discuss.elastic.co/t/how-to-create-a-new-line-on-a-kibana-canvas-table/248179/1 "2020-09-10T13:38:09Z")
Hello, I'm new here, and in kibana. I would like to know if it is possible and how it would be, to put a new line on a table. I made an essql query: ```auto essql:SELECT sum(segrelatorio) as value,severidade FROM "databases*" WHERE GROUP BY severidade ``` however it only returns 3 values for the field, which are: warning, high, disaster. what i would like to do, would be to put a new line as the name Normalized which would be a calculation of the 3 first. ---
**Author:** ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)\ **Post date:** [September 10, 2020, 4:53pm UTC](https://discuss.elastic.co/t/how-to-create-a-new-line-on-a-kibana-canvas-table/248179/2 "2020-09-10T16:53:44Z")
You might not be able to do this in ES SQL, but I think you can use another expression function to do this. Take a look at the [canvas function reference](https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html) to see the full list. ---
**Author:** ![francieliton\_araujo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/francieliton_araujo/32/47771_2.png) [@francieliton\_araujo](https://discuss.elastic.co/u/francieliton_araujo)\ **Post date:** [September 10, 2020, 5:38pm UTC](https://discuss.elastic.co/t/how-to-create-a-new-line-on-a-kibana-canvas-table/248179/3 "2020-09-10T17:38:51Z")
I understood and even read the documentation. I found a lot of interesting things, but I don't know how to do it, if you can do an example. If I managed to do 2 essql and the result was a union it would already solve, but I don't know. ---
**Author:** ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)\ **Post date:** [September 10, 2020, 6:07pm UTC](https://discuss.elastic.co/t/how-to-create-a-new-line-on-a-kibana-canvas-table/248179/4 "2020-09-10T18:07:19Z")
It would help if you could provide a sample input and sample output. ---
**Author:** ![francieliton\_araujo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/francieliton_araujo/32/47771_2.png) [@francieliton\_araujo](https://discuss.elastic.co/u/francieliton_araujo)\ **Post date:** [September 11, 2020, 5:16pm UTC](https://discuss.elastic.co/t/how-to-create-a-new-line-on-a-kibana-canvas-table/248179/5 "2020-09-11T17:16:09Z")
```auto filters | demodata | pointseries color="state" size="max(price)" | pie | render ``` ![disscur](https://us1.discourse-cdn.com/elastic/original/3X/6/a/6abdf7025e525a6640688c9b14e104d73f6d553b.png) This would be the example that I can do, however I would like to include a new slice, I wanted the result for example the total sum / quantity of slices. below follows how I would like it to look ![disscur2](https://us1.discourse-cdn.com/elastic/original/3X/8/5/859cf0d2c9977fcbaaa0a219dc7661e3beca5255.png) Ready this would be more or less what I wanted, this new slice would be a calculation in relation to the other, type 5000 - sum (start) + sum (running) + sum (done). ---
**Author:** ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)\ **Post date:** [September 14, 2020, 10:31pm UTC](https://discuss.elastic.co/t/how-to-create-a-new-line-on-a-kibana-canvas-table/248179/6 "2020-09-14T22:31:28Z")
There is no expression function that can insert rows into an existing table. You can insert columns, for example here is a fairly complex table I've made that fetches some number of rows and then calculates the percentage of each row as it relates to the overall total. ![Screen Shot 2020-09-14 at 6.30.12 PM](https://us1.discourse-cdn.com/elastic/original/3X/a/e/ae3a9a4926e27eab8c370063bf243b719a79d2bc.png) It's using the `var_set` and `var` features available in 7.7 and higher: ```auto filters | var_set name="results" value={essql query='SELECT DestCityName, COUNT(*) as count from kibana_sample_data_flights group by DestCityName'} | var name="results" | var_set name="sum" value={ply expression={math "sum(count)"} | getCell "value"} | var name="results" | staticColumn name="sum" value={var name="sum"} | mapColumn name="percent_of_sum" expression={math "count / sum" | formatnumber "0.0%"} | sort by="count" reverse=true ``` The alternative to var\_set is copy+pasting queries, but it's not as fast. ---
**Author:** ![francieliton\_araujo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/francieliton_araujo/32/47771_2.png) [@francieliton\_araujo](https://discuss.elastic.co/u/francieliton_araujo)\ **Post date:** [September 15, 2020, 12:07pm UTC](https://discuss.elastic.co/t/how-to-create-a-new-line-on-a-kibana-canvas-table/248179/7 "2020-09-15T12:07:25Z")
> [@wylie](#): > > `math "count / sum" ` Okay, thank you very much. it is an alternative solution. I have 3 questions that are: 1st =\> I can have two ``value = {essql query = 'SELECT DestCityName, COUNT (*) as count from kibana_sample_data_flights group by DestCityName'} | var name = "results"` ` And what will happen, we will have twice as many columns or double rows. 2nd =\> this expression `{math "sum (count)"}` can be changed to `{math "1000 - sum (count)"}` 3rd =\> could you give me an example if possible of how to put a condition in `mapColumn name = "percent_of_sum"` type if DestCityName = Zurich then `math "count / sum"` else `math "1000 - count / sum` ---
**Author:** ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)\ **Post date:** [September 15, 2020, 2:40pm UTC](https://discuss.elastic.co/t/how-to-create-a-new-line-on-a-kibana-canvas-table/248179/8 "2020-09-15T14:40:13Z")
1. You can have multiple variables using var\_set. For example ```auto | var_set name="a" value={essql query="..."} | var_set name="b" value={essql query="..."} ``` And then reference those as `| var name="a"` later. In the most recent version you can execute these in parallel using `var_set name="" value="" name="" value=""`. 1. Yes 2. Conditional logic can be put into the lazily evaluated function for mapColumn. Something like: ```auto | var name="a" | mapColumn name="new_column" expression={if {getCell "DestCityName" | eq "Zurich"} then={math "count / sum"} else={math "1000 - count / sum"}} ``` ---
**Author:** ![francieliton\_araujo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/francieliton_araujo/32/47771_2.png) [@francieliton\_araujo](https://discuss.elastic.co/u/francieliton_araujo)\ **Post date:** [September 15, 2020, 3:47pm UTC](https://discuss.elastic.co/t/how-to-create-a-new-line-on-a-kibana-canvas-table/248179/9 "2020-09-15T15:47:20Z")
> [@wylie](#): > > ```auto > filters > | var_set name="results" value={essql query='SELECT DestCityName, COUNT(*) as count from kibana_sample_data_flights group by DestCityName'} > | var name="results" > | var_set name="sum" value={ply expression={math "sum(count)"} | getCell "value"} > | var name="results" > | staticColumn name="sum" value={var name="sum"} > | mapColumn name="percent_of_sum" expression={math "count / sum" | formatnumber "0.0%"} > | sort by="count" reverse=true > > ``` thank you very much, I really believe that this will work, but I have already encountered a problem: when I try to make a query comes the following error ![voice](https://us1.discourse-cdn.com/elastic/original/3X/6/8/68485d40cc70c71570b9186575f2fc022fb0c93f.png) poderia me ajudar novamente ![v2](https://us1.discourse-cdn.com/elastic/original/3X/8/9/8925567447c0298f9c6f9e58a0045f4ec73305af.png) ---
**Author:** ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)\ **Post date:** [September 15, 2020, 5:05pm UTC](https://discuss.elastic.co/t/how-to-create-a-new-line-on-a-kibana-canvas-table/248179/10 "2020-09-15T17:05:29Z")
You need to escape special characters with double quotes in SQL. This usually looks like this: `essql query="select \"geo.src\" from kibana_sample_data_logs"`. ---
**Author:** ![francieliton\_araujo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/francieliton_araujo/32/47771_2.png) [@francieliton\_araujo](https://discuss.elastic.co/u/francieliton_araujo)\ **Post date:** [September 21, 2020, 5:32pm UTC](https://discuss.elastic.co/t/how-to-create-a-new-line-on-a-kibana-canvas-table/248179/11 "2020-09-21T17:32:55Z")
> [@wylie](#): > > `| mapColumn name="new_column" expression={if {getCell "DestCityName" | eq "Zurich"} then={math "count / sum"} else={math "1000 - count / sum"}}` Thank you very much solved my problem using an alternative exit, sorry for the delay in responding, but I need to test and apply because I would not leave you alone, because I knew you were close. Thank you very much ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [October 19, 2020, 5:33pm UTC](https://discuss.elastic.co/t/how-to-create-a-new-line-on-a-kibana-canvas-table/248179/12 "2020-10-19T17:33:10Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.