# Range in data table is not working

**URL:** <https://discuss.elastic.co/t/range-in-data-table-is-not-working/262490>\
**Category:** Kibana\
**Created:** [January 28, 2021, 11:40am UTC](https://discuss.elastic.co/t/range-in-data-table-is-not-working/262490 "2021-01-28T11:40:37Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![tahseen\_fatima](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tahseen_fatima/32/67789_2.png) [@tahseen\_fatima](https://discuss.elastic.co/u/tahseen_fatima)\
**Post date:** [January 28, 2021, 11:40am UTC](https://discuss.elastic.co/t/range-in-data-table-is-not-working/262490/1 "2021-01-28T11:40:37Z")

</div>

Hi,

I have a ELK setup of 7.10.0v.  
I created a data table visualization with Range in bucket, but it is not giving the correct value.

PFA of the data table with out range.  
Here it is givin the total U-clients **"126,903"**

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/2/d/2dd33f7b191774a9eacaa312ed00f95fce6abf91.png)  
Also please find the request query:

> {  
> "aggs": {  
> "1": {  
> "cardinality": {  
> "field": "branch\_name.keyword"  
> }  
> },  
> "4": {  
> "sum": {  
> "field": "brk\_amt"  
> }  
> },  
> "5": {  
> "cardinality": {  
> "field": "ent\_id.keyword"  
> }  
> },  
> "6": {  
> "cardinality": {  
> "field": "dealer\_name.keyword"  
> }  
> }  
> },  
> "size": 0,  
> "stored\_fields": [  
> "\*"  
> ],  
> "script\_fields": {},  
> "docvalue\_fields": [  
> {  
> "field": "@timestamp",  
> "format": "date\_time"  
> },  
> {  
> "field": "creat\_dt",  
> "format": "date\_time"  
> },  
> {  
> "field": "ent\_dob",  
> "format": "date\_time"  
> },  
> {  
> "field": "long\_trade\_date",  
> "format": "date\_time"  
> },  
> {  
> "field": "mapping\_date",  
> "format": "date\_time"  
> },  
> {  
> "field": "trade\_date",  
> "format": "date\_time"  
> }  
> ],  
> "\_source": {  
> "excludes":   
> },  
> "query": {  
> "bool": {  
> "must": ,  
> "filter": [  
> {  
> "match\_all": {}  
> },  
> {  
> "range": {  
> "trade\_date": {  
> "gte": "2020-12-29T11:37:07.904Z",  
> "lte": "2021-01-28T11:37:07.904Z",  
> "format": "strict\_date\_optional\_time"  
> }  
> }  
> }  
> ],  
> "should": ,  
> "must\_not":   
> }  
> }  
> }

After applying the Range filter on brokerage the total U-clients value got increased by **"37075"**

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/a/9/a9b6e9a9c5acf828746856b5558b903df2e3b2de.png)  
Request query with range:

> {  
> "aggs": {  
> "3": {  
> "range": {  
> "field": "brk\_amt",  
> "ranges": [  
> {  
> "from": 0,  
> "to": 199  
> },  
> {  
> "from": 200,  
> "to": 499  
> },  
> {  
> "from": 500  
> }  
> ],  
> "keyed": true  
> },  
> "aggs": {  
> "1": {  
> "cardinality": {  
> "field": "branch\_name.keyword"  
> }  
> },  
> "4": {  
> "sum": {  
> "field": "brk\_amt"  
> }  
> },  
> "5": {  
> "cardinality": {  
> "field": "ent\_id.keyword"  
> }  
> },  
> "6": {  
> "cardinality": {  
> "field": "dealer\_name.keyword"  
> }  
> }  
> }  
> }  
> },  
> "size": 0,  
> "stored\_fields": [  
> "\*"  
> ],  
> "script\_fields": {},  
> "docvalue\_fields": [  
> {  
> "field": "@timestamp",  
> "format": "date\_time"  
> },  
> {  
> "field": "creat\_dt",  
> "format": "date\_time"  
> },  
> {  
> "field": "ent\_dob",  
> "format": "date\_time"  
> },  
> {  
> "field": "long\_trade\_date",  
> "format": "date\_time"  
> },  
> {  
> "field": "mapping\_date",  
> "format": "date\_time"  
> },  
> {  
> "field": "trade\_date",  
> "format": "date\_time"  
> }  
> ],  
> "\_source": {  
> "excludes":   
> },  
> "query": {  
> "bool": {  
> "must": ,  
> "filter": [  
> {  
> "match\_all": {}  
> },  
> {  
> "range": {  
> "trade\_date": {  
> "gte": "2020-12-29T11:39:24.750Z",  
> "lte": "2021-01-28T11:39:24.750Z",  
> "format": "strict\_date\_optional\_time"  
> }  
> }  
> }  
> ],  
> "should": ,  
> "must\_not":   
> }  
> }  
> }

Kindhly help me to fix this issue..

Regards,  
Tahseen

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [January 28, 2021, 12:29pm UTC](https://discuss.elastic.co/t/range-in-data-table-is-not-working/262490/2 "2021-01-28T12:29:47Z")

</div>

The `U-Clients` column is using the cardinality aggregation, which is done separately per bucket. There are 126,903 unique `branch_name`s, but some of them are in multiple ranges, so the sum of all cardinalities per range is higher than the number of unique terms in the total dataset, as some of them are showing up multiple times.

I recommend creating a separate metric visualization to calculate the cardinaltiy of the whole data set and placing it next to the table on a dashboard.

---

<div class="post-metadata">

**Author:** ![tahseen\_fatima](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tahseen_fatima/32/67789_2.png) [@tahseen\_fatima](https://discuss.elastic.co/u/tahseen_fatima)\
**Post date:** [January 28, 2021, 12:47pm UTC](https://discuss.elastic.co/t/range-in-data-table-is-not-working/262490/3 "2021-01-28T12:47:53Z")

</div>

Hi @flash1293

Thank you for the quick response,  
I got your point.  
My next qery is -  
If I want to take the range of **"sum of brokerage"** in data table.  
How can I plot in Bucket?

Regards,  
Tahseen

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [January 28, 2021, 1:16pm UTC](https://discuss.elastic.co/t/range-in-data-table-is-not-working/262490/4 "2021-01-28T13:16:45Z")

</div>

Could you open a separate post for this so people have an easier time finding the topic later on?

---

<div class="post-metadata">

**Author:** ![tahseen\_fatima](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tahseen_fatima/32/67789_2.png) [@tahseen\_fatima](https://discuss.elastic.co/u/tahseen_fatima)\
**Post date:** [January 28, 2021, 1:39pm UTC](https://discuss.elastic.co/t/range-in-data-table-is-not-working/262490/5 "2021-01-28T13:39:42Z")

</div>

Hi @flash1293

Yes okay.

Thanks,  
Tahseen

---

<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:** [February 25, 2021, 1:39pm UTC](https://discuss.elastic.co/t/range-in-data-table-is-not-working/262490/6 "2021-02-25T13:39:43Z")

</div>

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