Hi, in my index I'm holding transaction data and I'm storing (among other stuff) fields transactionType
and amount
. I'm interested in Transaction types 1 (withdraw) and 2 (deposit). I'm trying to visualize the following data (percentage):
[(SUM of amount where transactionType is 1) / (SUM of amount where transactionType is 2)] * 100
I was thinking of creating a metric type of visualization (SUM on field amount
) and then split it using filters on transactionType
(1 and 2). Next, I need a new field with formula from above.
I know of Bucket Script Aggregation (didn't try it yet, need it in visualization) but how can it be used in a visualization?
Any help is appreciated, thanks in advance.
EDIT here is the query which returns what I expect:
{
"aggs": {
"all_matching_docs": {
"filters": {
"filters": {
"all": {
"match_all": {}
}
}
},
"aggs": {
"withdraws": {
"filter": {
"term": {"transactionType": 1}
},
"aggs": {
"amount": {
"sum": {"field": "amount"}
}
}
},
"deposits": {
"filter": {
"term": {"transactionType": 2}
},
"aggs": {
"amount": {
"sum": {"field": "amount"}
}
}
},
"prt": {
"bucket_script": {
"buckets_path": {
"withdraws_sum": "withdraws>amount",
"deposits_sum": "deposits>amount"
},
"script": "(params.deposits_sum / params.withdraws_sum) * 100",
"format": "0,00 %"
}
}
}
}
},
"size": 0,
"query": {
"bool": {
"must": [
{
"range": {
"eventTime": {
"format": "strict_date_optional_time",
"gte": "2019-04-01T22:00:00.000Z",
"lte": "2019-05-01T22:00:00.000Z"
}
}
}
],
"filter": [
{
"bool": {
"should": [
{
"match": {
"status": 0
}
}
],
"minimum_should_match": 1
}
}
]
}
}
}