[Help] Dividing two columns without Aggregation

Elastic Stack Kibana
1

Hello all,

i'm enjoying using kibana but in the last few days i'm struggling in achiving a task which I thought is going to be simple.

I have an index which have couple of fields indexed: ['revenue', 'spend'].. in order to create profit field to display in data table I just had to create a scripted field to calc revenue - spend..
so far so good. but when i'm trying to get margin.. which is profit / revenue, I can't do that.

is there any approach to achive a new field which will calc :

((total_revenue_for_all_docs - total_spend_for_all_docs) / total_revenue_for_all_docs ) * 100
and not aggregating anything after that.

best is going to be in data table. any another approach will be blessed.

sample dev tools query which succeed but I cannot manage it to happen in visualization:

GET /data_collector/_search
{ 
    "size": 0,
              "query": {
            "bool": {
              "must": [
                {
                  "match": {
                    "campaign.keyword": "censored"
                  }
                },
                {
                  "range": {
"date": {
            "gte": "now/d",
            "lt": "now+1d/d"
          }
                  }
                }
              ]
            }
          },
    "aggs" : {
        "sales_per_month" : {
            "date_histogram" : {
                "field" : "date",
                "interval" : "day"
            },
            "aggs": {
                "total_revenue": {
                    "sum": {
                        "field": "revenue"
                    }
                },
                                "total_spend": {
                    "sum": {
                        "field": "spend"
                    }
                },
                "margin": {
                    "bucket_script": {
                        "buckets_path": {
                          "totalRevenue": "total_revenue",
                          "totalSpend": "total_spend"
                        },
                        "script": "def margin = (params.totalRevenue - params.totalSpend) / params.totalRevenue * 100; return (double)Math.round(margin * 100) / 100;"
                    }
                }
            }
        }
    }
}

thanks!

2

You can use bucket scripts and math by using Visual Builder. I'd recommend using the Math aggregation to calculate the margin.