Range in data table is not working

Elastic Stack Kibana
1

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
image976×101 18.4 KB

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
image953×158 35.6 KB

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

2

The U-Clients column is using the cardinality aggregation, which is done separately per bucket. There are 126,903 unique branch_names, 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.

3

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

4

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

5

Hi @flash1293

Yes okay.

Thanks,
Tahseen

6

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