SUM for colum1 and filter

Elastic Stack Kibana
1

I am trying to do the following:

Select Sum (durationprev) from ptx*-… where location = abc0011 and status = auto as running

AVAL= running / total.

filters 
| essql 
  query="SELECT SUM(duration) AS running
FROM \"data-*\"
WHERE QUERY ('ptx')
AND event = 'ab'

AND status ='AUTO'"

| math "running"
    | metric "Countries" 
      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"} metricFormat="0,0.[000]"
    | render

in general: that is the sum of column A (time) and in another column B has AUTO and MANUEL status.
And I just want the sum total of the status AUTO and I don't want to filter with "

AND

" can it be done? and I hope that I have explained myself.
could someone please help me?.

2

It looks like it should work. Whats the error message / issue?
Do you need more examples of Canvas boards?

3

yes, I do, I need to see more examples of Canvas board, Thanks a lot. there is not a error or issue, but I want to have the SUM total (duration) AS running and too the SUM (duration) but only of the STATUS=AUTO and then I could to do the percentage in "metric" (SUM (duration, status=auto) / running. I need to get the percentage of the duration between auto status.

4

okay got it.

In this case I would do a group by status within the SQL statement. Then you have the SUM of duration per status in the response.
Next step is using filterrows to get the value of each status. You could store this e.g. in a variable.
In another variable you save the result of the math expression that is doing the sum of both.

Finally you insert your percentage calculation in your visualization.

I think the best example you can fine here. That's one I've made a while ago to help users with their first steps in Canvas.

5

First of all I must clarify that I do not know anything about programming, I am very new to these issues and before seeing your message, I tried to do this but I don't know if it is a good option. to use the variables in math, this would be missing, right?

filters
| var_set name="running" value={
    essql query="SELECT SUM(duration) FROM \"data-*\" WHERE QUERY ('ptx') AND event = 'ab' AND statu ='AUTO' "
  }
| var_set name="total" value={
    essql query="SELECT SUM(duration) FROM \"data-*\" WHERE QUERY ('ptx') AND event = 'ab' "
  }
|math ("running / total") (how to do ?)

Thanks advance

6

This should do it. Not sure if it is the shortest way but it will work
math {string {var running} "/" {var total}}

7

unfortunately it didn't work

8

Ich think you need to change this part a bit, as you do not extract the field to be stored in the variable.

Try something like this:
essql query="SELECT SUM(duration) as duration FROM "data-*" WHERE QUERY ('ptx') AND event = 'ab' AND statu ='AUTO' " | getCell duration

9 
    filters
| var_set name="running" value={
    essql query="SELECT SUM(duration) AS duration FROM \"data-*\" WHERE QUERY ('ptx') AND statu ='AUTO' " | getCell duration
  }
| var_set name="total" value={
    essql query="SELECT SUM(duration) AS total FROM \"data-*\" WHERE QUERY ('ptx') AND event = 'data' " | getCell total
  }
| math {string {var running} "/" {var total}}

Thanks for the recommendation but this is not working, it is a shame because it is an important piece of information and it cannot be generated in this program.

10

hi i tried this with MARKDOWN and it almost worked. I say Almost, if I just delete the variable "all", the other variable shows it is progress but not satisfactory. Could you help me because it does not take the second variable and the mathematical operation is performed.

var_set "running" 
  value={filters | essql query="SELECT SUM(duration) AS part FROM \"data-*\" WHERE QUERY ('ptx') AND status ='AUTO'" | getCell column="part "}
var_set "total" 
  value={filters | essql query="SELECT SUM(duration) AS time FROM \"data-*\" WHERE QUERY ('ptx') " | getCell column="time "}
| filters
| markdown {var "running/ total"}
  font={font align="center" color="#444444" family="'Open Sans', Helvetica, Arial, sans-serif" italic=false size=30 underline=false weight="bold"}
| render
11

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