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

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 ?

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 ?

Hi Guillaume_Dufrenne

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

Hi FIFI

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

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.