How to create an histogram with many aggregations on X axis and a formula on Y axis

[FIFI]

Hi,

Goal:
I would like to create a histogram
With two aggregations on X axis. One over terms. One over time.
With one aggregation on Y axis. One over terms
With a formula to compute value (using lens formula : sum(value, kql='data_kind : "kind1" ')/sum(value, kql='data_kind : "kind2" '))

Attempts:
I tried with aggregation based visualization (succeed aggregation over X and Y axis, but didn't find any ways to apply formula)
I tried with lens visualization (succeed aggregation over Y, and applied formula, but didn't find any ways to apply two aggregations over X)

Configuration
elastic 7.17
kibana 7.17

documents mapping

{
    @timestamp: date, # to aggregate over x 
    simulation_id: keyword, # to aggregate over x 

    field_to_aggregate_on_y: keyword, # to aggregate over y 
    data_kind: keyword, # used in lens formula 
    value: float
}

Any help ?

[Guillaume_Dufrenne]

Hello FiFi
Are you trying to create an histogram on which
The X axis represent the time,
The Y axis represent the metric of "value"
And you break down the metric by term ?

Can you elaborate on how you want to apply the aggregation over time ? What is the expected result ?

[FIFI]

Hi Guillaume_Dufrenne

Thank you for answering

I would expect something like the following histogram.

So
we have two aggregations on X axis. One over time break down by terms.
we have one aggregation on Y axis, by terms.

But
In this histogram, the cells values are not correct.
I would like to include a formula to compute the values of each cell.
Which is the following in lens visualization
sum(value, kql='data_kind : "kind1" ')/sum(value, kql='data_kind : "kind2" ')

For each bucket of aggregations (over X and Y), I would like two more aggregations to compute sum, and a third to compute the ratio. (which I can obtain using formula tab in lens visualization)

Thank you

[Jacqueline_Jacquelot]

Hi FIFI

According to your documentation, that this is not possible.
You have to switch to Tableau.