Calculate sum of piecewise linear aggregations on y axis

Elastic Stack Kibana
1

Is there a way to represent/calculate the following for y axis :
count_1 = count(rows where condition=1)
count_2 = count(rows where condition=2)
...
sum_1 = 100 if count_1<100 else count_1
sum_2 = 100 if count_2<100 else count_2
...
return (sum_1 +sum_2...)

I've looked into using new runtime fields but since the calculation requires knowledge of the state of the overall table, it doesn't seem to work.

2

Hello @MagnesiumReroll

Welcome to the community.

Could you please give a sample example with data for better understanding of the requirement & how you want to display it on dashboard?

Thanks!!

3

Hi @MagnesiumReroll

you can use ES|QL for this kind of tasks.
Here's an example query that reflects your example:

FROM index
| STATS
  count_1 = count() where extension == "deb",
  count_2 = count() where extension == "zip"
| EVAL 
  sum_1 = CASE(count_1 < 100, 100.0, count_1),
  sum_2 = CASE(count_2 < 100, 100.0, count_2)
| EVAL sum = sum_1 + sum_2
| KEEP sum
1 Like