# How to calculate the standard deviation in a transform?

**URL:** <https://discuss.elastic.co/t/how-to-calculate-the-standard-deviation-in-a-transform/368845>\
**Category:** Elasticsearch\
**Tags:** transforms\
**Created:** [October 15, 2024, 11:46am UTC](https://discuss.elastic.co/t/how-to-calculate-the-standard-deviation-in-a-transform/368845 "2024-10-15T11:46:13Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![kishorkumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kishorkumar/32/132930_2.png) [@kishorkumar](https://discuss.elastic.co/u/kishorkumar)\
**Post date:** [October 15, 2024, 11:46am UTC](https://discuss.elastic.co/t/how-to-calculate-the-standard-deviation-in-a-transform/368845/1 "2024-10-15T11:46:13Z")

</div>

I am trying to create a transform report, but I'm unable to create a range based on the `avg(total)`.

For example, I want to assign customers into a bucket based on their average total spend, like the range `$0-$100`.

Can you help me generate a list of customers whose average total falls within this range?

I wanted to create the monthly report of customers.

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/b/e/be27cb945a1836d87a9196471787d168e6e426b4.png)

#transforms #Elastic Stack

---

<div class="post-metadata">

**Author:** ![kishorkumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kishorkumar/32/132930_2.png) [@kishorkumar](https://discuss.elastic.co/u/kishorkumar)\
**Post date:** [October 15, 2024, 11:46am UTC](https://discuss.elastic.co/t/how-to-calculate-the-standard-deviation-in-a-transform/368845/2 "2024-10-15T11:46:38Z")

</div>

From #Kibana to #Elasticsearch

---

<div class="post-metadata">

**Author:** ![kishorkumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kishorkumar/32/132930_2.png) [@kishorkumar](https://discuss.elastic.co/u/kishorkumar)\
**Post date:** [October 15, 2024, 11:46am UTC](https://discuss.elastic.co/t/how-to-calculate-the-standard-deviation-in-a-transform/368845/3 "2024-10-15T11:46:38Z")

</div>

Removed #visualisation

---

<div class="post-metadata">

**Author:** ![Patrick\_Whelan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/patrick_whelan/32/135049_2.png) [@Patrick\_Whelan](https://discuss.elastic.co/u/Patrick_Whelan)\
**Post date:** [October 15, 2024, 9:02pm UTC](https://discuss.elastic.co/t/how-to-calculate-the-standard-deviation-in-a-transform/368845/4 "2024-10-15T21:02:30Z")

</div>

What does the field look like in the mapping? If it is just a number, you could use `avg` on it as is: [Avg aggregation | Elasticsearch Guide [8.15] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-avg-aggregation.html)

Something like this would create buckets maintaining every customers averages, and then you could search over it for the desired range

```auto
POST _transform/_preview
{
  "source": {
    "index": "customer-orders"
  },
  "pivot": {
    "group_by": {
      "customer_bucket": {
        "terms": {
          "field": "customer",
          "missing_bucket": true
        }
      }
    },
    "aggs": {
      "avg_total": {
        "avg": {
          "field": "total"
        }
      }
    }
  }
}

```

Or something like this would use Bucket Selector to record the value in the range that you want: [Bucket selector aggregation | Elasticsearch Guide [8.15] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-selector-aggregation.html)

```auto
POST _transform/_preview
{
  "source": {
    "index": "customer-orders"
  },
  "pivot": {
    "group_by": {
      "customer_bucket": {
        "terms": {
          "field": "customer",
          "missing_bucket": true
        }
      }
    },
    "aggs": {
      "avg_total": {
        "avg": {
          "field": "total"
        }
      },
      "avg_total_bucket_filter": {
        "bucket_selector": {
          "buckets_path": {
            "avg_total": "avg_total"
          },
          "script": "params.avg_total >= 0 && params.avg_total <= 100"
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![kishorkumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kishorkumar/32/132930_2.png) [@kishorkumar](https://discuss.elastic.co/u/kishorkumar)\
**Post date:** [October 16, 2024, 6:32am UTC](https://discuss.elastic.co/t/how-to-calculate-the-standard-deviation-in-a-transform/368845/5 "2024-10-16T06:32:35Z")

</div>

Hi @Patrick_Whelan

i think i have missed the point i wanted to calculate the standard devitition in transfrom.

i have achieved the average and sum.

Thank you for this guide.

---

<div class="post-metadata">

**Author:** ![Patrick\_Whelan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/patrick_whelan/32/135049_2.png) [@Patrick\_Whelan](https://discuss.elastic.co/u/Patrick_Whelan)\
**Post date:** [October 16, 2024, 1:24pm UTC](https://discuss.elastic.co/t/how-to-calculate-the-standard-deviation-in-a-transform/368845/6 "2024-10-16T13:24:03Z")

</div>

Transforms does not support `extended_stats` which calculate the standard deviation in the search request. Though standard deviation can be approximated via the supported `percentiles` or `median_absolute_deviation`, both are probably the easiest approach to this? Alternatively, they can be recalculated using a `scripted_metric`, though that can significantly reduce a search request's performance depending on the script.

If a normal distribution is given, `percentiles` will return about the same as the standard deviation in `extended_stats`.

- [Percentiles aggregation | Elasticsearch Guide [8.15] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-percentile-aggregation.html)
- [Median absolute deviation aggregation | Elasticsearch Guide [8.15] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-median-absolute-deviation-aggregation.html)

---

<div class="post-metadata">

**Author:** ![kishorkumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kishorkumar/32/132930_2.png) [@kishorkumar](https://discuss.elastic.co/u/kishorkumar)\
**Post date:** [October 17, 2024, 1:05pm UTC](https://discuss.elastic.co/t/how-to-calculate-the-standard-deviation-in-a-transform/368845/9 "2024-10-17T13:05:59Z")

</div>

Using scripted\_metric its possibe to calculate that thanks for the guide