[Lens] Formula: Conditionals

[flash1293]

Right now formula is limited to math operations. However, having conditionals (branching logic) is also often helpful.

Implementation

• Extend the tinymath grammar by the following binary operators:
  • a < b - compiles to lt(a,b)
  • a <= b - compiles to lte(a,b)
  • a > b - compiles to gt(a,b)
  • a >= b - compiles to gte(a,b)
  • a == b - compiles to eq(a,b)
• Implement these functions in the regular tinymath lib
• Implement an if function in the regular tinymath lib: if(<cond>,<then>, <else>) - if the value of cond is truthy, return then, otherwise return else
• Whitelist them in Lens

This requires to extend the parsing logic in Lens to consider the data type of an AST node and check the integrity and report errors. The type can be inferred by walking the tree in a depth first search and return type information for all functions.

Use cases

In some cases, it's possible to do the conditional in a runtime field, but it's easier/more straight forward to do it in a formula, because we don't have to split the logic
Examples:

• default values for calculations which would fail otherwise, e.g. average revenue per customer but in some cases customer id is not provided which counts as additional customer: sum(total)/(cardinality(customer_id) + if(count() > count(filter=customer_id:*), 1, 0)
  • Could be replaced by runtime field 'real_customer_id': if(customer_id) customer_id else 'UNKNOWN_CUSTOMER_ID'
• do different calculations based on the current bucket value, e.g. API request utilization in percentage per day, but there's different quota on weekends: count() / if(last_value(day_of_week) < 5, 1000 /* weekday quota */, 500 /* weekend quota */)
  • Could be replaced by runtime field 'quota_usage': if(day_of_week < 5) 1/1000 else 1/500

In some cases, it's not possible to calculate in a runtime field:
Examples:

• calculations which follow rules based on aggregated numbers, e.g. business KPI based on customer size: if(count(order_id:*) > 10000, /* calculation for large customers */, /* calculations for small customers */)
• only doing a calculation if an entity is in a certain state, e.g. average used memory of currently busy servers: if(last_value(load) > 0, average(memory), 0)

There are other separate use cases which require string handling (returning a string literal or compare against a string literal (separate issue for this)

[elasticmachine]

Pinging @elastic/kibana-app (Team:KibanaApp)

[wylieconlon]

These examples seem great, but I think they are missing the most common case that I know of: hiding "small" values of series, like the ones described in #86190, by converting them into null values. This would require us to add a null literal. This type of formula typically copy+pastes the same value, which is one of the things that users dislike about timelion.

if(average(bytes) > 0, average(average(bytes), null): hide zeroes

Another example that I would have liked to see is a range of values, if the upper and lower boundary matters:

if(count() > 100, if(count() < 1000, count(), null), null): count is between 100 and 1000

For both of these use cases, I would prefer a helper function which is a nicer syntax. Maybe something like show_if(count(), gt=0) would be a generic way to express the kind of number range conditions that I've seen most often.

[flash1293]

Agreed, that's a relevant use case @wylieconlon . I like the show_if, otherwise it can get quite messy.

This can be a separate issue, but I think for categorical axes we should provide some way to hide the null rows in the chart, otherwise nulling them doesn't help much. Like this:
Show table of average bytes per server, row split: top values of server, metric if(average(bytes) > 0, average(bytes), null)

This will show the following table:

server	formula
server1	-
server2	45
server3	-
server4	12

What I want to see

server	formula
server2	45
server4	12

Maybe this can be a setting on the bucket dimension (a switch for "hide if <metric> is not defined")

[ghudgins]

+1 https://discuss.elastic.co/t/how-can-kibana-compare-two-fields/282918/5

[ghudgins]

+1 https://discuss.elastic.co/t/boolean-comparison-using-lens-formula/285872

[MarianAlexandruAlecu]

Another great example that I want to implement right now, but can't is:

• number of days/week a user is active

E.g.

• 200 users are active 5 days per week
• Average active days per week per user is 2

[flash1293]

@MarianAlexandruAlecu you might be able to solve this case with the new "Collapse by" feature that will be released with 8.3: #131748

[probance-antoine]

How does the condition formula work after all ? I can't find any documentation on this

[ghudgins]

@probance-antoine this issue is still open so we have yet to implement it

[ghudgins]

editorialized quote: "solves 100+ use cases for us...very hard to get approval to add conditionals to ingest processing so this feature would really help"