# Data table visualization with custom aggregation query
**URL:** https://discuss.elastic.co/t/data-table-visualization-with-custom-aggregation-query/138637
**Category:** Kibana
**Created:** [July 5, 2018, 5:42am UTC](https://discuss.elastic.co/t/data-table-visualization-with-custom-aggregation-query/138637 "2018-07-05T05:42:02Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@hjSohn](https://discuss.elastic.co/u/hjSohn)
#### Post date: [July 5, 2018, 5:42am UTC](https://discuss.elastic.co/t/data-table-visualization-with-custom-aggregation-query/138637/1 "2018-07-05T05:42:02Z")
Hello Team,
I am using ES to check chatbot's response success rate. (document contains message and tag)
I want to visualize that
- total count : the number of documents that contain `@send` in `customTags` keyword field
- failed count : the number of documents that contain `@fail` in `customTags` keyword field
- success count : (total count - failed count)
So I composed a query like below :
```json
{
"query": {
"bool": {
"filter": [
{
"match": {
"sender": 10000
}
}
]
}
},
"aggs": {
"report": {
"date_histogram": {
"field": "@timestamp",
"interval": "60m"
},
"aggs": {
"send": {
"filter": {
"match": {"customTags": "@send"}
},
"aggs": {
"count": {
"sum": {
"field": "sender"
}
}
}
},
"fail": {
"filter": {
"match": {"customTags": "@fail"}
},
"aggs": {
"count": {
"sum": {
"field": "sender"
}
}
}
},
"success": {
"bucket_script": {
"buckets_path": {
"total": "send > count",
"fail": "fail > count"
},
"script": "(params.total - params.fail)/10000"
}
}
}
}
}
}
```
As you can see, "send" and "fail" aggregations contain "sum aggregation" for calculation.
So my questions are
1. Is it fine to use query like that to calculate with results?
2. Could I setup this query using `visualization UI`? I tried many times but didn't find good one.
For the reference I attach the response of query below.
```json
{
"took": 22,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 125105,
"max_score": 0,
"hits": []
},
"aggregations": {
"whole_result": {
"buckets": [
{
"key_as_string": "2018-06-26T15:00:00.000Z",
"key": 1530025200000,
"doc_count": 4294,
"fail": {
"doc_count": 86,
"count": {
"value": 8600
}
},
"send": {
"doc_count": 173,
"count": {
"value": 17300
}
},
"success": {
"value": 87
}
},
...
```
Thanks for help in advance!
---
### Author:  [@tylersmalley](https://discuss.elastic.co/u/tylersmalley)
#### Post date: [July 6, 2018, 2:10pm UTC](https://discuss.elastic.co/t/data-table-visualization-with-custom-aggregation-query/138637/2 "2018-07-06T14:10:58Z")
Reaching out to the visualization team regarding this.
---
### Author:  [@tylersmalley](https://discuss.elastic.co/u/tylersmalley)
#### Post date: [July 6, 2018, 8:43pm UTC](https://discuss.elastic.co/t/data-table-visualization-with-custom-aggregation-query/138637/3 "2018-07-06T20:43:03Z")
Actually, I looked into this. Here is my sample data:
```auto
DELETE /discuss-138637
PUT /discuss-138637
{
"mappings": {
"_doc": {
"properties": {
"@timestamp": {
"type": "date"
},
"customTags": {
"type": "keyword"
}
}
}
}
}
POST /discuss-138637/_doc
{
"@timestamp": "2018-07-06T00:00:00",
"customTags": ["@send"]
}
POST /discuss-138637/_doc
{
"@timestamp": "2018-07-06T00:01:00",
"customTags": ["@fail"]
}
POST /discuss-138637/_doc
{
"@timestamp": "2018-07-06T00:02:00",
"customTags": ["@send"]
}
POST /discuss-138637/_doc
{
"@timestamp": "2018-07-06T00:03:00",
"customTags": ["@send"]
}
POST /discuss-138637/_doc
{
"@timestamp": "2018-07-06T00:04:00",
"customTags": ["@send"]
}
POST /discuss-138637/_doc
{
"@timestamp": "2018-07-06T00:05:00",
"customTags": ["@fail"]
}
POST /discuss-138637/_doc
{
"@timestamp": "2018-07-06T00:06:00",
"customTags": ["@send"]
}
POST /discuss-138637/_doc
{
"@timestamp": "2018-07-06T00:07:00",
"customTags": ["@send"]
}
POST /discuss-138637/_doc
{
"@timestamp": "2018-07-06T00:08:00",
"customTags": ["@fail"]
}
POST /discuss-138637/_doc
{
"@timestamp": "2018-07-06T00:09:00",
"customTags": ["@send"]
}
POST /discuss-138637/_doc
{
"@timestamp": "2018-07-06T00:10:00",
"customTags": ["@send"]
}
```
Let me know if this is not what you're looking for . But I believe there are a few ways to do this.
The first is with filters. This is ok if you know all the possible values:
The second is with an aggregation. Note you will need to select "Show total" under options.
---
### Author:  [@hjSohn](https://discuss.elastic.co/u/hjSohn)
#### Post date: [July 9, 2018, 7:49am UTC](https://discuss.elastic.co/t/data-table-visualization-with-custom-aggregation-query/138637/4 "2018-07-09T07:49:34Z")
Hello @tylersmalley,
I'm really appreciate about the answer but my original question is about calculation with result. You show me that `N(all) = N(@send) + N(@fail)` but how about this? `N(something) = N(@send) - N(@fail)`. I just want to visualize the data through subtraction. Could it be possible?
Please help me again~!
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [August 6, 2018, 7:49am UTC](https://discuss.elastic.co/t/data-table-visualization-with-custom-aggregation-query/138637/5 "2018-08-06T07:49:35Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.