Clamp function for two dynamic value doesn't work

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?

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,

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}

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:

1 Like

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?

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