Clamp function for two dynamic value doesn't work

Elastic Stack Kibana
1

Hi everyone,

I saw this post and tried.

To get max of fieldA and fieldB,
I tried Formula:
clamp(max(fieldA), max(fieldB), 10000),
but it does not worked and the cell remains empty.

in my case, max(fieldA)=3 and max(fieldB)=5, then clamp(max(fieldA), max(fieldB), 100000) must be 5, isn't it? Is something wrong?

image
image1414×468 28.5 KB

clamp(3,5,10000) returns 5.

I tried this formula because the formula doesn't work fine if the difference is less than 1.

When max(fieldA)=4.8 and max(fieldB)=5 for example,

image
image2368×304 36.6 KB 

clamp(max(fieldA)-max(fieldB),0,1)*max(fieldA) + clamp(max(fieldB)-max(fieldA),0,1)*max(fieldB)

returns 0 + (5-4.8)*5 = 1. It must be 5.

How to reproduce:
case1:

PUT /test_lens_formula/

POST /test_lens_formula/_bulk
{"index":{}}
{"fieldA":3, "fieldB":5}
{"index":{}}
{"fieldA":1, "fieldB":2}

case2:

PUT /test_lens_formula2/

POST /test_lens_formula2/_bulk
{"index":{}}
{"fieldA":4.8, "fieldB":5}
{"index":{}}
{"fieldA":1, "fieldB":2}
2

Hi @Tomo_M

one simple trick would be to multiply the first argument of both clamp by an arbitrary big number you're sure it would make the difference > 1.
For instance in your example you could use 100:

clamp( 100 * (max(fieldA)-max(fieldB)) ,0,1)*max(fieldA) + clamp(100 * (max(fieldB)-max(fieldA)) ,0,1)*max(fieldB)

Using 100 even if the difference between the 2 max is 0.2, it will become 20, which will be converted by 1.
As I mention in the post, the clamp is a convenient "trick" rather than a final solution. :sweat_smile:

3

Thank you for a nice workaround! It may cover a lot more cases.

Do you have any idea about why clamp(max(fieldA), max(fieldB), 100000) returns empty?