Kibana Formula using ifelse with multiple conditions

Elastic Stack Kibana
1

How can I combine multiple conditions in an ifelse statement? I need to avoid a situation where I divide by zero e.g. any of the following values: count() and count(shift='1w')+count(shift='2w') may not be 0 to avoid a N/A value which causes the wrong Dynamic coloring to be applied to it...

I am calculating the deviation of my current transactions with historic values: count()-((count(shift='1w')+count(shift='2w'))/2). Negative percentages show a decrease, positive an increase...

2

Hello,

For deviation can we try using derivatives ?

3

Alas - not an option for me: I am working in Lens and am limited to the formulas available to me there (using 8.6.2 at present)

4

Do you have the final formula?

5

No - I did not manage this. Still hoping someone can answer. Doc is poor in this regard.

6

sorry, I meant if you can write a draft formula, just to make us better understand your problem that would be useful.

I'll assume a general case here, so I would recommend 2 possible solutions:

  • if a function can return null (so not 0 like count()) then theres the defaults( fn(...), 1) to coalesce null values to 1
  • the alternative for functions who never returns null but you want to avoid 0, there's the ifelse function that can be used

For instance a simple 0 fallback to avoid 0 division can be the following:

5 / ifelse( count() > 0, count(), 1)