# Canvas Multiple Math Functions ESSQL

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 & This one

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:

``````| 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"?

Hi @wylie

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``````

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