# Moving average on derivative

**URL:** <https://discuss.elastic.co/t/moving-average-on-derivative/138764>\
**Category:** Elasticsearch\
**Created:** [July 5, 2018, 3:25pm UTC](https://discuss.elastic.co/t/moving-average-on-derivative/138764 "2018-07-05T15:25:34Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![uyth](https://avatars.discourse-cdn.com/v4/letter/u/6a8cbe/32.png) [@uyth](https://discuss.elastic.co/u/uyth)\
**Post date:** [July 5, 2018, 3:25pm UTC](https://discuss.elastic.co/t/moving-average-on-derivative/138764/1 "2018-07-05T15:25:34Z")

</div>

Elasticsearch version: 6.2

I'm trying to get the moving average of a derivative of the remaining bytes. However, I want to exclude positive results from the derivative. I have tried to add a bucket\_selector, but it gives me a null\_pointer\_exception on "params.change". This is the query that includes positive derivatives:

```
POST user_profile/_search
{
  "size": 0,
  "query": {
    "match": {
      "user_key": "this_key"
    }
  },
  "aggs": {
    "history": {
      "nested": {
        "path": "history"
      },
      "aggs": {
        "last_week": {
          "filter": {
            "range": {
              "history.request_time": {
                "gte": "2018-04-13T06:30:39",
                "lte": "2018-04-20T06:30:39"
              }
            }
          },
          "aggs": {
            "bucket_by_day": {
              "date_histogram": {
                "field": "history.request_time",
                "interval": "day"
              },
              "aggs": {
                "max_remaining_bytes_of_day": {
                  "max": {
                    "field": "history.value.remainingBytes"
                  }
                },
                "balance_change": {
                  "derivative": {
                    "buckets_path": "max_remaining_bytes_of_day",
                    "gap_policy": "insert_zeros",
                    "unit": "hour"
                  }
                },
                "burndown_estimation": {
                  "moving_avg": {
                    "buckets_path": "balance_change.normalized_value"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

```

This is what I tried to do:

```
POST user_profile/_search
{
  "size": 0,
  "query": {
    "match": {
      "user_key": "this_key"
    }
  },
  "aggs": {
    "history": {
      "nested": {
        "path": "history"
      },
      "aggs": {
        "last_week": {
          "filter": {
            "range": {
              "history.request_time": {
                "gte": "2018-04-13T06:30:39",
                "lte": "2018-04-20T06:30:39"
              }
            }
          },
          "aggs": {
            "bucket_by_day": {
              "date_histogram": {
                "field": "history.request_time",
                "interval": "day"
              },
              "aggs": {
                "max_remaining_bytes_of_day": {
                  "max": {
                    "field": "history.value.remainingBytes"
                  }
                },
                "balance_change": {
                  "derivative": {
                    "buckets_path": "max_remaining_bytes_of_day",
                    "gap_policy": "insert_zeros",
                    "unit": "hour"
                  }
                },
                "burndown_estimation": {
                  "moving_avg": {
                    "buckets_path": "balance_change.normalized_value"
                  }
                },
                "balance_change_filter": {
                  "bucket_selector": {
                    "buckets_path": {
                      "change": "balance_change"
                    },
                    "script": "params.change < 0"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}
```

---

<div class="post-metadata">

**Author:** ![uyth](https://avatars.discourse-cdn.com/v4/letter/u/6a8cbe/32.png) [@uyth](https://discuss.elastic.co/u/uyth)\
**Post date:** [July 6, 2018, 8:45am UTC](https://discuss.elastic.co/t/moving-average-on-derivative/138764/2 "2018-07-06T08:45:40Z")

</div>

Solved by using bucket\_script to remove positive entries and getting the moving average on the bucket\_script aggregation

```
"max_data_of_bucket": {
  "max": {
    "field": "history.remainingBytes"
  }
},
"derivative_of_balance": {
  "derivative": {
    "buckets_path": "max_data_of_bucket",
    "unit": "hour"
  }
},
"burndown": {
  "bucket_script": {
    "buckets_path": {
      "balance_change": "derivative_of_balance.normalized_value"
    },
    "script": """
      if (params.balance_change > 0) {return null;}
      else {return params.balance_change;}
    """
  }
},
"moving_average_of_burndown": {
  "moving_avg": {
    "buckets_path": "burndown",
    "gap_policy": "skip",
    "window": 7,
    "model": "ewma",
    "settings": {
      "alpha": 0.4
    }
  }
}
```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 3, 2018, 8:45am UTC](https://discuss.elastic.co/t/moving-average-on-derivative/138764/3 "2018-08-03T08:45:45Z")

</div>

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