I have tabular data by districts and days. Each row contains diff between the current and previous days, i.e. relative value.
district | day | metric
----------+------------+--------
D1 | 2020-04-12 | -11
D1 | 2020-04-13 | 40
D2 | 2020-04-13 | 20
D1 | 2020-04-14 | 11
D1 | 2020-04-15 | -50
I need to visualize this data on Kibana's region map.
So, the metric is grouped by district and summarized. Here is the ElasticSearch query generated by Kibana:
{"aggs": {
"2": {
"terms": {
"field": "district",
"size": 300,
"order": {
"1": "desc"
}},
"aggs": {
"1": {
"sum": {
"field": "metric"
}}}}},
"query": {
"bool": {
"must": [{
"range": {
"@timestamp": {
"format": "strict_date_optional_time",
"gte": "2020-04-12T00:00:00.0Z",
"lte": "2020-04-16T00:00:00.0Z"
}}}]}}}
Depending on the selected data rage the result will vary. For example, this query selects all data (see @timestamp filter) and metric values for districts D1 and D2 will be -10 and 20. If I'll change the filter to select data for 12-13 April it will be -11 + 40 = 29 for D1 and 20 for D2.
In Kibana I need to filter out buckets with negative sum(metric) value and show districts only with a positive sum value. I couldn't find any working solution. I've tried
- Kibana's JSON input + Bucket Selector
Aggregation
Visual - Kibana's Vega Graphs, but it seems that it doesn't support region maps.
I'd like to avoid it as it's quite complicated. - Build a new index based on the existing ones, but it's not possible as
a result depends on a date range filter, so I cannot pre-calculate metrics and filter out negatives because I don't know what date range is in advanced
Nothing of this worked for me. I was able to compose a working Elastic Search query that does exactly what I want, but I don't know how to visualize it using the region map:
{"aggs": {
"2": {
"terms": {
"field": "district",
"size": 300,
"order": {
"1": "desc"
}},
"aggs": {
"1": {
"sum": {
"field": "metric"
},
"1_bucket_selector": { -- here is main part, how get it in region map?
"bucket_selector": {
"buckets_path": {
"metricSum": "1"
},
"script": "params.metricSum > 0"
}}}}}}}
So, any workaround to achieve what I want?
Here is related topic on SO