Hi!
I'm looking for a feature that I don't think is possible to do now, but I hope it will come in the future. We are using Kibana dashboards to get insights into our sales performance. I want to be able set a variable in the dashboard ( f ex total sales in this time range) and be able to use this variable in lens formulas to get share of sales. Is this something that will be possible with ESQL?
Hello @tallakh
Could you please try to provide an example for better clarity related to the requirement?
Thanks!!
I'm doing something which looks a bit similar where I can select a field name from the dashboard and inject this field name in the ES|QL request.
FROM person
| STATS count = COUNT() BY ??field
| LIMIT 100
I'm doing that in Elastic Serverless. Not sure if it's possible in 9.0. You might have to wait for 9.1???
IMHO it's a super nice feature that have been asked for years and is now here...
F ex in a lens table that has split rows on categories, I want to be able to show the share of total sales for each category. This is possible to an extent using a formula like sum(total_revenue) / overall_sum(sum(total_revenue)) but that does not work when filtering down on one of the categories.
I think it would be very powerful to be able to set a dashboard variable like total_sales = sum(total_revenue) that ignore any filters, which can be used in a formula f ex sum(total_revenue) / {{total_sales}}
Thanks David, that looks super useful! I've been wondering if this feature would solve my need. But initially it doesn't look like it will. I'll have to play around with it.
Yeah. It's a bit new but it opens yet the door to new use cases.
Hi again David! I'm trying to implement the field that you set and I'm not sure how I can do that. Is it a control element, or something else? I'm running elastic stack 9.2, and I fail to find documentation on this 
Yeah. It's a bit hidden IMO.
I can find it when creating a dashboard, and adding a Panel ES|QL, then when writing the ES|QL query, it sometimes appears. See "Create control" in the above screenshot.
Which gives at the end something like:
HTH
BTW: I'm running a 9.0.1 version.
Thanks! I found this github PR that explains the feature. Any idea on how to create a control that uses the values from a ES|QL result?
I don't know yet 
May be @Stratoula_Kalafateli could tell?
You can start typing a query like
FROM index | WHERE field ==
and then select "Create control" and you will be able to write an ES|QL query to retrieve the values
1 Like