Scripted field with division does not show result

[doume06 (d.massot@neopost.com)]

HI,
I have created a very simple scripted field in Kibana 5 to make the division of two other fields (numeric) and the result doesn't show up.

The scripted field is defined this way:

I tried to replace the division (/) by a sum (+) and it works fine.
I tried to replace the doc['qty_population'].value by a value like 1234 and it is OK too.
So why the division of two fields is not working ?

Thanks a lot for your help.
Dominique

[LeeDr (Lee Drengenberg)]

When you says it's not working, what are you getting in Kibana? 0? Some incorrect value? An error?

[LeeDr (Lee Drengenberg)]

If you're getting an error when you go to Discover in Kibana, please click the More Info button and copy/paste it here. If you're getting an error there, it could be that you have some docs in this index which don't have a value (or have 0 value) for qty_population.
It's pretty easy to check by filtering on that field existing, or by filtering on it's value.
If it's the case, you can work-around that with a little fancier scripting in your scripted field.

[doume06 (d.massot@neopost.com)]

Hi Lee,
Thanks a lot for your time.
Actually I found the root cause of my problem.
The two fields work_cost_eur and qty_population are sometimes empty, sometimes equal to 0, sometimes filled with correct values but they are not filled with correct values in the same record. In some records you have a work cost, in others you have population but never in the same record so the division will always fail if it is done at record level. For a certain period, I must do the sum of the work costs, then the sum of the population and then divide the first by the second.
A tip on how do do that in Kibana ?
Thanks again for your help.

[doume06 (d.massot@neopost.com)]

To illustrate, I want to divide the green by the blue, periods are months:

And display it with bars.

[LeeDr (Lee Drengenberg)]

I would suggest the Visual Builder visualization.

Here's one post where we do a ration based on a filter;

Doing Math over count of events

Here's one option if you're on a recent version of Kibana that has the Visual Builder visualization type. I know it's in 5.4.0 and later. go to the Panel Options tab and set your index pattern (here I'm using metricbeat-*); [image] back on the Data tab select Filter Ratio [image] I picked metricset.name:process as my numerator, but yours would be something like transactionStatus:Success [image] The Gauge tab shows a single value and there some options to format as a percent, change…

But you need to sum specific values, so I think you need to do it a little different. This post uses the calculation aggregation;

How to add 2 metrics visualization?

If you are using 5.4 you can achieve this with the Time Series Visual Builder. https://www.elastic.co/guide/en/kibana/current/_featured_visualizations.html You can do many calculations on aggregations. For example, here is a comparison of average bytes calculated automatically vs manually: automatically: [image] Running the calculations manually: [image]

Let me know if this works for you.

Regards,
Lee

[doume06 (d.massot@neopost.com)]

Hi Lee,
I tried Visual Builder but in your second example above (manually) I have the calculation choice which is greyed, probably because I need a child aggregation ? (parent pipeline aggregation requires child aggregation)
I guess this is not my case. Is it mandatory to have this child aggregation ?

[LeeDr (Lee Drengenberg)]

I think this should get you the ratio of your 2 metrics.

1. Create a Sum aggregation for the sum of the first metric
2. Create a Sum aggregation for the sum of the second metric
3. Create a Calculation aggregation, create 2 variables to represent the 2 sums above, put the ratio of the 2 in the Painless Script field. Make sure to put params. before your variable names

[doume06 (d.massot@neopost.com)]

Hi Lee,
I tried it but I reach a limitation.
My costs and population can be displayed MONTHLY in regular Kibana:

But when I go in Visual Builder I can only display my costs and populations WEEKLY (if I try monthly I get this message: Max buckets exceeded: 527039 is greater than 2000, try a larger time interval in the panel options. Strange as Regular Kibana can do it)

The problem is that my populations are an average for a month but stored in the first week of the month:

SO I cannot divide costs per population because sometimes populations values are empty.
Maybe I should do all these calculation in my index. Like this it will be easy to display.

Thanks again for your help anyway.
Maybe you have a tip for the monthly display in Visual Builder.

[LeeDr (Lee Drengenberg)]

Did you try 1m (one minute) or 1M (one Month)?

[doume06 (d.massot@neopost.com)]

Hi Lee,
Indeed I was using 1m as I thought it was 1month.
I used 1M instead and it works just fine.
Thanks a lot for your help, it was of great help.
Cheers.
PS: I will probably have other questions/feedback. Is the forum the right place to post messages or not ?

[LeeDr (Lee Drengenberg)]

Glad it worked for you!

Yes, this is the best place. The Kibana engineering team monitors this forum and tries to make sure users get a response (either from us or from other users). So feel free to subscribe to new posts and help if you can

Regards,
Lee

[Gwendaline_p (Gwendaline)]

Hi Lee ,

For people who don't have a recent version (for example, I have the 4.5.4), how can I do the same thing please ?

Kind regards ,
Gwendaline

[LeeDr (Lee Drengenberg)]

Hi Gwendaline,

Sorry, but I don't think there is a way built in to 4.5.4. Someone might be able to create a custom plugin, but that would be beyond my current skills.

The latest Kibana release was 5.5.2 and 6.0.0-beta2 was recently released, so there won't be any new features added to 4.x or 5.x versions of Kibana. It would be best if you (or your company) could upgrade. Kibana developers are currently working on features for the 6.1 release.

Regards,
Lee