# Canvas Multiple Math Functions ESSQL **URL:** \ **Category:** Kibana\ **Tags:** canvas\ **Created:** [September 21, 2020, 4:54am UTC](https://discuss.elastic.co/t/canvas-multiple-math-functions-essql/249302 "2020-09-21T04:54:20Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![karnamonkster](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/karnamonkster/32/67266_2.png) [@karnamonkster](https://discuss.elastic.co/u/karnamonkster)\ **Post date:** [September 21, 2020, 4:54am UTC](https://discuss.elastic.co/t/canvas-multiple-math-functions-essql/249302/1 "2020-09-21T04:54:21Z")
Hi, I have a typical scenario where we need to get a percentage of number of **OK devices**. Something like **Scenario 1** - Query Defective Devices count - Divide ( Defective Devices / total) There are 2 ESSQL queries which get me the **Defective device** & **Total** Here is my attempt where i am successfully able to get the percentage of Defective devices. ``` filters "live" | essql query="SELECT COUNT(DISTINCT b4) as scount FROM \"myindex\" WHERE QUERY(' b4:/AT[0-9]+/ AND value:1')" | math {string "scount/" {filters | essql query="SELECT COUNT(DISTINCT b4) as pcount FROM \"myindex\" WHERE QUERY('b4:/AT[0-9]+/')" | math "pcount"}} | formatnumber "0%" format="0,0.[0]%" | metric "Defective Devices" metricFont={font family="'Tw Cen MT', Helvetica, Arial, sans-serif" size=72 align="center" color="red" weight="bold" underline=false italic=false} labelFont={font family="'Dubai Light', Helvetica, Arial, sans-serif" size=30 align="center" color="#FFFFFF" weight="normal" underline=false italic=false} metricFormat="0,0.[000]%" | render ``` Now I wish to use the same logic to get the % of **OK Devices**. Should be straight away subtracting (100 - result ) but for some reason it does not work. **Scenario 2** - Subtract (total - defective) = Ok Devices - Divide ( OK devices/ total) Here is my unsuccessful attempt which only gives me the count of **OK Devices** ``` filters "live" | essql query="SELECT COUNT(DISTINCT b4) as pcount FROM \"myindex\" WHERE QUERY('b4:/AT[0-9]+/')" | math {string "pcount -" {filters | essql query="SELECT COUNT(DISTINCT b4) as scount FROM \"myindex\" WHERE QUERY(' b4:/AT[0-9]+/ AND value:1')" | math {string "scount -" {filters | essql query="SELECT COUNT(DISTINCT b4) as dcount FROM \"myindex\" WHERE QUERY('b4:/AT[0-9]+/')" | math "count(dcount)"}}}} | formatnumber "0%" format="0,0.[000]%" | metric "Ok Devices" metricFont={font family="'Tw Cen MT', Helvetica, Arial, sans-serif" size=72 align="center" color="#4fbf48" weight="bold" underline=false italic=false} labelFont={font family="'Dubai Light', Helvetica, Arial, sans-serif" size=30 align="center" color="#FFFFFF" weight="normal" underline=false italic=false} metricFormat="0,0.[000]" | render ``` Is there any way i can add another math string expression to achieve the final result that is **OK Device** percentage ? Referred [This link](https://discuss.elastic.co/t/sql-dividing-2-values-from-2-queries-in-canvas/222381) & [This one](https://discuss.elastic.co/t/kibana-canvas-calculate-percentage-using-to-aggregations/190702/4) ---
**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 21, 2020, 2:53pm UTC](https://discuss.elastic.co/t/canvas-multiple-math-functions-essql/249302/2 "2020-09-21T14:53:09Z")
I think there is probably a simple answer answer a more-complex answer. The simple answer is that you appear to be doing `| math "count(dcount)"`, which is going to always return one because there is one cell. You might want to do `| getCell "dcount"` which will return a numeric value instead of a table. There is a more complex answer, which is that starting in Kibana 7.7 you can use variables to assign and fetch values from context. Instead of making 3 SQL queries as you're showing here, you can make two. Here's how: ```auto | var_set name="pcount" value={ essql query="SELECT COUNT(DISTINCT b4) as pcount FROM \"myindex\" WHERE QUERY('b4:/AT[0-9]+/')" } | var name="pcount" | math ``` I know that I haven't provided an exact answer yet, and it's because I think you are writing the wrong SQL queries. If you are still seeing the wrong results, I can only help if you break down: Which query represents "total"? Which one represents "defective"? ---
**Author:** ![karnamonkster](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/karnamonkster/32/67266_2.png) [@karnamonkster](https://discuss.elastic.co/u/karnamonkster)\ **Post date:** [September 22, 2020, 5:42am UTC](https://discuss.elastic.co/t/canvas-multiple-math-functions-essql/249302/3 "2020-09-22T05:42:40Z")
Hi @wylie Thanks for your reply, Well we are running v7.5.2 Cluster for ES & Kibana and would not be upgrading the same till end of this year. On the SQL queries here they are. **Defective Devices** `"SELECT COUNT(DISTINCT b4) as scount FROM \"myindex\" WHERE QUERY(' b4:/AT[0-9]+/ AND value:1')"` **Total Devices** `SELECT COUNT(DISTINCT b4) as pcount FROM \"myindex\" WHERE QUERY('b4:/AT[0-9]+/')"` An update on the last Post - While working on the issue ( **SCENARIO 1** ), i did play around the math & string function to get a number (not in percentage %). For example: Defective Device % = 3.6% Number i get for OK Devices = 96.4 ``` filters "live" | essql query="SELECT COUNT(DISTINCT b4) as scount FROM \"myindex\" WHERE QUERY(' b4:/AT[0-9]+/ AND value:1')" | math {string "100 - scount/" {filters | essql query="SELECT COUNT(DISTINCT b4) as pcount FROM \"myindex\" WHERE QUERY('b4:/AT[0-9]+/')" | math "pcount/100"}} | formatnumber "0%" format="0,0.[0]%" | metric " OK Devices" metricFont={font family="'Tw Cen MT', Helvetica, Arial, sans-serif" size=72 align="center" color="#4fbf48" weight="bold" underline=false italic=false} labelFont={font family="'Dubai Light', Helvetica, Arial, sans-serif" size=30 align="center" color="#FFFFFF" weight="normal" underline=false italic=false} metricFormat="0.0a" | render ``` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [October 20, 2020, 5:42am UTC](https://discuss.elastic.co/t/canvas-multiple-math-functions-essql/249302/4 "2020-10-20T05:42:45Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.