# How to create a chart using a calculated value from fields?

**URL:** <https://discuss.elastic.co/t/how-to-create-a-chart-using-a-calculated-value-from-fields/81723>\
**Category:** Kibana\
**Created:** [April 9, 2017, 7:48pm UTC](https://discuss.elastic.co/t/how-to-create-a-chart-using-a-calculated-value-from-fields/81723 "2017-04-09T19:48:11Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![elasticheart](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/elasticheart/32/65189_2.png) [@elasticheart](https://discuss.elastic.co/u/elasticheart)\
**Post date:** [April 9, 2017, 7:48pm UTC](https://discuss.elastic.co/t/how-to-create-a-chart-using-a-calculated-value-from-fields/81723/1 "2017-04-09T19:48:11Z")

</div>

Hi,

I am using ELK GA 5.0.0. I have an elasticsearch index, having a string field `datax`. That field has values like `DataA`, `DataB`, `DataC`, `DataE`, and `DataE`.

I have created a line chart, In Y-Axis, I selected Count. In X axis, I added aggregation of date Histogram. Then I did **Split Lines -\> Sub Aggregation -\> Filters** and added `DataA`, `DataB`, and `DataC` so that I get count of logs containing these values only. Then I made another filter and added `DataE`, and `DataE`, so that the line shows count of these 2 fields only.

The graph looked similar to the image below;

![](https://us1.discourse-cdn.com/elastic/original/3X/1/4/148b1ec1f1894a2e299cb64eac3161d8f446f070.png)

What I am trying to achieve is not this. I want a single line chart. The value of line should be (`DataA`+`DataB`+`DataC`) / (`DataD`+`DataE`) . For example, at time T, if the first line has a count of 20, and second line has a count of 2, I want my line to show 20 / 2, which is 10.

Is this possible, or is there any workaround?

Thanks in advance.

---

<div class="post-metadata">

**Author:** ![weltenwort](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/weltenwort/32/53885_2.png) [@weltenwort](https://discuss.elastic.co/u/weltenwort)\
**Post date:** [April 11, 2017, 1:30pm UTC](https://discuss.elastic.co/t/how-to-create-a-chart-using-a-calculated-value-from-fields/81723/2 "2017-04-11T13:30:12Z")

</div>

Hi @elasticcloud,

I think there is misunderstanding of how the "filter" aggregation works. It partitions the data into buckets that each match the given filter. So to create a filter that separates `DataA` and `DataB` from `DataC` you would create one filter with `datax:(DataA OR DataB)` and another one with `datax:DataC`. With Kibana 5.0 you can not calculate the difference between those counts in a visualization though.

You should be able to achieve this using a Timelion-based "Timeseries" visualization, roughly:

```
.es(q="datax:(DataA OR DataB OR DataC)", index="YOURINDEX", metric="count").divide(.es(q="datax:(DataD OR DataE)", index="YOURINDEX", metric="count"))
```

---

<div class="post-metadata">

**Author:** ![elasticheart](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/elasticheart/32/65189_2.png) [@elasticheart](https://discuss.elastic.co/u/elasticheart)\
**Post date:** [April 11, 2017, 1:35pm UTC](https://discuss.elastic.co/t/how-to-create-a-chart-using-a-calculated-value-from-fields/81723/3 "2017-04-11T13:35:53Z")

</div>

Hi @weltenwort , yea I understand. Could you tell me how to get rid of divide by zero? If `0`, I want to display the result as `-1`. How can I do that?

---

<div class="post-metadata">

**Author:** ![weltenwort](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/weltenwort/32/53885_2.png) [@weltenwort](https://discuss.elastic.co/u/weltenwort)\
**Post date:** [April 11, 2017, 1:59pm UTC](https://discuss.elastic.co/t/how-to-create-a-chart-using-a-calculated-value-from-fields/81723/4 "2017-04-11T13:59:45Z")

</div>

You can use a combination of `.if()` and `.multiply` for that. So let's abbreviate `.es(q="datax:(DataA OR DataB OR DataC)", index="YOURINDEX", metric="count")` as `S1` and `.es(q="datax:(DataD OR DataE)", index="YOURINDEX", metric="count")` as `S2` for the sake of this explanation. Then the resulting query could look like this:

```
S1.divide(S2.if("eq", 0, S1.multiply(-1)))
```

---

<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:** [May 9, 2017, 2:05pm UTC](https://discuss.elastic.co/t/how-to-create-a-chart-using-a-calculated-value-from-fields/81723/5 "2017-05-09T14:05:02Z")

</div>

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