How to apply range using calculated metric field

[kishorkumar]

I wanted to create the buckets for the total sales on the basis of the calculated value form metric aggs in lens
i have total field in doc

so if use agg of avg in lens then i want to create the range built on top of the return value this way i will have the avg_bucket of the range of total

and second the count of the customer fall in that avg_range bucket.

like this

is it possible without transfrom?

[Marco_Liberati]

Hi @kishorkumar

that looks like what it is called a "custom range" interval in Lens.
To build configure an Interval and click on create custom ranges:

at this point configure manually all the different intervals:

[kishorkumar]

@Marco_Liberati

ks like what it is called a "custom range" interval in Lens.
To build configure an Interval and click on create custom ranges:

i think you have missed the point here i know there custom ranges and interval i wanted to utilize the resutlt of the metric aggs not the field directly.

[Marco_Liberati]

Sorry, I think I still do not fully understand what's your request then.
Can you clarify your request?

[kishorkumar]

@kishorkumar

top of the return value

Yes, here is the output i am looking for i know by transform its achieveable. but i want if its possible via TSVB/Lens

Suppose we have a field called total and customerId, and each customer has multiple orders in the index.

We need a monthly-wise visualization like this:

Average Monthly Sales Bucket Range

Bucket Name	# of Customers
$0 - $10	41
$11 - $20

Now, we will create a table visualization using Lens / TSVB

In the metric aggregations, I will add the avg(total) and unique_count(customerId). Now, I want to create a range bucket from the returned value of this avg(total) metric. I don’t want to create the range on the total field that already exists. Instead, I want to utilize the avg(total).

How we can do that in Lens / TSVB?

[Marco_Liberati]

Ok, I think I've got it.
Interval buckets cannot be computed on top of aggs, so this won't be possible with regular Lens.
What you could do instead is use an ES|QL table visualization with a query like this:

FROM index 
| stats t = avg(total) by customer_id 
| stats c = COUNT_DISTINCT(customer_id) by rangeFrom = (v / 500)::integer
| sort rangeFrom
| eval range = concat(
  to_String(rangeFrom * 500),
  "-",
  to_string((rangeFrom + 1) * 500)
  )
| rename c as `# of Customers`, range as Bucket
| keep Bucket, `# of Customers`

I've built something along the lines with bytes and agent in place of total and customer_id but the final result was this:

Note that 500 is the size of the interval, so adjust it to your use case.
As for empty buckets, that's the limit of the query at the moment.

[kishorkumar]

Yes this was possible but issue i have 8.9 version kibana and its in new version of kibana then i will have to update the kibana first

but thanks you for this