[Lens] Formula: References

[flash1293]

For various things it would be to nice to reference other columns of the current table in a formula.

Implementation

Technically it's very easy to read a value from another column as the formula is executed per row and it's only necessary to know the column id, but as it's a UUID this would be a very unintuitive for the user to type directly.

Instead, we could add a reference(<string argument>) function which resolves the reference at parsing time (as soon as the user submits the formula).

• User types 5 + reference("The other number column")
• Parser walks the ast and encounters reference node
• The first argument is looked up in the label map to resolve to a column id (e.g. a3b2fc)
• Using the position information in the ast, the string the user typed is replaced by the id: 5 + reference(a3b2fc)
• In the generated math operation the reference function call is removed: 5 + a3b2fc
• Column a3b2fc is mentioned in the list of references of the math operation
• This math operation can be executed in the expression

If the user opens the editor later

• Saved string of the formula is parsed and walked 5 + reference(a3b2fc)
• Parser encounters the reference function call
• First argument is looked up by column id
• Column label is obtained and added into the string using the position information in the ast `5 + reference("The other column")
• Changed formula is shown to the user

If the referenced column is deleted, an error can be shown the user because the references are not consistent anymore.
The column label of the referenced column can change, but the formula will stay functional

Use case

• Build formula on top of each other (e.g. define a formula, then use the value in a second formula). This is common is excel-like usage, especially with data tables. It's easier to understand what's happening for the user and also some of the intermediary steps are useful as well, eliminating the need of specifying them multiple times
• Reference the value of a bucket (e.g. in a conditional)
• Define the columns to group by for overall metrics [Lens] Formula: Overall metrics #94597

[elasticmachine]

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

[wylieconlon]

I assume from the context that we would limit references to visible dimensions only, not hidden functions like the count that's inside of a cumulative sum?

The only concern I have is the potential for circular references. We need to prevent users from building formulas that reference each other.

[flash1293]

Yes, this is about visible dimensions only.

Checking for circular references is a real problem and checking for them has to become part of the validation logic. If there are some, the chart fails to render.

[dej611]

+1 https://discuss.elastic.co/t/percent-change-from-variable-selections/327864/3

[markov00]

In order to provide better transparency of priorities, issues that will not be prioritized within the next 24 months are being closed.

Tracking request in Lens general improvements ice box, solvable possibly with ESQL #184648