Multiple Aggregations/UI Filters are not applied in ES|QL Visualizations

Elastic Stack Kibana
,
1

I have an index that is of the form:

| event   | hall     | date       | average_attendees |
|---------|----------|------------|-------------------|
| Event A | visitors | 2022-01-01 | 150               |
| Event A | main     | 2022-01-01 | 250               |
| Event B | rotary   | 2022-01-01 | 100               |
| Event A | main     | 2022-01-02 | 300               |
| Event B | main     | 2022-01-02 | 350               |

I want to display a table in Kibana that shows the average daily attendees per-event.

Crucially, this requires two "group-by" operations

So, using the table above:

  • Event A's Jan 1st Average is: (150+350)/2=200
  • Event B's Jan 1st Average is: 250
  • Event A's Jan 2nd Average is: 300
  • Event B's Jan 2nd Average is: 350

That is the first group-by. The second one would aggregate across days, ending up with a table of the form:

| event   | avg_attendees |
|---------|---------------|
| Event A | 250           |
| Event B | 225           |

Using ES|QL, this would be something like:

FROM index |
STATS daily_avg = AVG(attendees) BY event, date |
STATS avg_attendees=AVG(daily_avg) BY event

Simple enough so far.

My Problem:

In Kibana, my users can adjust the global filters, either by date or by event... but when that happens, the ES|QL table doesn't change?

I think based on this it seems like it's not supported?

Is there any way I can have a dynamically updating table that also uses two group by 's?

I know there are breakdown's in the native "table" visualization, but that only does it once. Can I stack those?

Is there any solution for what I want to do?