Lens custom calculations/formula

[AlonaNadler]

Lens supports multiple calculation functions and that lists will grow to add additional functions including pipeline aggregations.

Problem:
Even with many more functions, there are still different needs that users are raising which require the ability to create a custom formula, adding their own logic to the calculation in a way Lens cannot expect.

Examples:

• Users who want to visualize profit subtracting one field with another over time. Sum(revenue) - sum(costs) - in daily intervals
• Users who want to know the ratio of two fields, for example (Sum(revenue) - sum(costs))/sum(revenue) or 1- sum(costs)/sum(revenue)
• Or other time comparison calculations (today(bytes) - yesterday(bytes)) /yesterday(bytes))
• Sum(bytes)*10

Goal:
To address these needs Lens should offer a way to go beyond the set calculation and provide a custom way to create formulas using fields, functions and mathematical operations. The formula can be visualized using any one of the charts in Lens and should be simple to use.

Approach:
Create a builder experience in Lens which is similar to writing an excel formula, allowing users to refer to fields in their index and combine them with mathematical functions and math operations. The syntax will be simple and not solely targeted for developers allowing users who are familiar with spreadsheets similar capability.

To make the creation of formulas easier and to help users learn about the formula. Users can start using the quick a function and move to the builder tab. In the builder tab, they can see how the function is already applied on the field, allowing them to tweak the function without the need to start from scratch, which results in an easier experience and provide a chance to learn in the product.

The builder will also allow users of Lens to create multi-steps aggregations like the one they might be able to create in TSVB which combines an unlimited number of steps. positive(derivative(average(altitude))

The ability to create multi-steps aggregations in Lens using the formula allows us to support our power users without adding complexity to the UI. The builder will provide users with autocomplete which will suggest fields in the index, math functions and allow them to use math operations.

In addition, the builder will provide a way to see what input a function requires, and provide a way to select from drop-down functions and the names of the fields without the need to type them for users

What this approach solves:

• Ability to have multiple steps calculations like in TSVB without turning the UI to complex to support this need
• Ability to customize the formula based on users’ unique needs which cannot be provided with regular function.

What are the drawbacks of this approach:

• Yet another language a user might need to learn (besides KQL/Lucene, EQL, Kibana expressions).
• Another language increases maintenance effort significantly.

Open questions:

• Aggregations vs operations on individual fields before aggregating?
• Transformation of fields whether it is in this custom calculations or maybe it is a separate utility that you can do from the fields list before going into the configurator
• What sort of calculation we don’t want to allow (can’t support or prefer not to support)

Technical questions:

• Would this language be something supported by Elasticsearch or something we would convert in Kibana into Query DSL?

cc: @timroes @wylieconlon @mbondyra @cchaos @rayafratkina

[elasticmachine]

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

[wylieconlon]

I think this issue is combining a few elements that need to be split apart to make progress on. I intend to close this issue and open several more issues based on it. You've identified some important goals, and I think we need to have more discussion in separate issues about how we will meet these goals.

Unified system with both simple and advanced mode

You have proposed that users should be able to go from the "quick" functions to a set of more advanced functions. To do this, we will need the architecture for calculations, and the linked issue contains some specific requirements beyond what you've listed.

Ability to do time-based comparison

Because time is already part of Lens visualizations due to the time picker, I've outlined a way to use hidden queries to do time offsets.

How much typing should users do?

You've proposed that we support a text-based grammar for calculations. While this is an option, I think we need to discuss the tradeoffs in a separate issue. The main questions to answer are:

• Is a text-based grammar the best user interface, as opposed to a form-based interface?
• How much overlap in functionality is there with other grammars that we already maintain, such as the expression language, TinyMath, and painless?

Should users be aware that some calculations happen inside Elasticsearch or in Kibana?

Based on the features we've discussed, especially time-based comparisons, we can't do every calculation inside Elasticsearch. Because of the architectural requirements, we might have to make users aware of some of these limits.

---

I think this list of questions covers almost all of the requirements from the original issue here, and I will link back to this closed issue from each of those.