How to show ratio of 2 fields which are result of 2 filters in a bar chart

[ppseal]

Hi,

I am looking to have two different sum numbers on the same field but on different filters. The filed is related to trade status which can be 'successful', 'not successful', 'incomplete', 'partly done' etc. I am looking to sum on "trade quantity" for two of the above trade statuses and then I need to perform a ratio of those two. How can that be done?

I was thinking to use Metric->Advanced-> json input to create a filtered and same again for another filtered sum and then a 3rd metric to show the ratio using painless.

[ppisljar (Peter Pisljar)]

You could do that in either timelion or time series visual builder

in timelion something like this:
.es(*, metric=sum:field1).divide(.es(*, metric=sum:field2))

and in tsvb you should use the calculation aggregation.

[ppseal]

Hi,

The issue is - I would like to be able to do the aggregation by any 'terms', not just by time. How can I do that?

If I want to bucket by 'client' or 'trader' and want to see a ratio of traded quantity/total order volume. How can that be achieved?

Tx

[ppisljar (Peter Pisljar)]

unfortunately calculations are not yet supported for non-time based series

[ppseal]

I have finally been able to use the json field to do some maths, like:

{
"script": {
"inline": "doc['Price'].value*doc ['Notional'].value",
"lang": "painless"
}
}

and that works which is great,

Price and Notional are two aggregate fields in Y-Axis.

All I now need to know is how to filter along with calc. i.e.

a) Only want Price field to do the aggregation when another field NegotiationStatus=X
b) Only want Notional field to do the aggregation when another field NegotiationStatus=Y

Would you be able to help with that plz?

[ppseal]

HI Peter,

Would you be able to help with the above?

Tx,

[ppisljar (Peter Pisljar)]

i am not really sure, but maybe you could do it with scripted fields (management->index patterns->scripted fields)

so you could create a field 'PriceWithNegotiationStatusOK' with a script like:

if (doc['NegotiationStatus'].value == 'OK') return doc['price'].value * doc['notional'].value;
else return 0; 

then you can aggregate this field as you would a normal field, however it is much more expensive to run such aggregations in comparison to aggregations on real fields.

[ppseal]

Hi Peter,

Thanks for coming back - so I tried by creating couple of scripted fields which had the required filters built-in.

however, I am unable to write a painless script on top of it in the 'Advanced->json' part of the chart.

It is failing. When I open the 'request' section of the visualization to see what's happening the scripted fields got expanded in the request to the underlying painless definition.

As a result when I refer the scripted fields by their name in advanced-json section, it can't find those fields.

You mentioned it is doable using Timevisualization as well.

Would you be able to suggest how I can filter and aggregate together there?

[ppisljar (Peter Pisljar)]

going back to your original question ...

so you have documents with

• status: ok/notok/something
• sales: number

and you want to show one serie for each status and sum the sales for each of them ?
thats doable by simply selecting split series, using filter aggregation and defining your filters for status field
then adding the sum metric on the sales

to do the ratio part you can use timelion or time series visual builder

in tsvb: create two series: use filter agg and sum metric for both of them (but with different filters)
then add calculation aggregation and divide the two previously defined seris

for timelion, super simple
.es(q=status:ok, index=myindex, metric=sum:sales).divide(.es(q=status:nok, index=myindex, metric=sum:sales))

[ppseal]

Regarding timelion: I tried the below:
.es(q=NegotiationStatus:Traded,split=Book:5,metric=sum:dyn_float_USDDV01).divide(.es(q=NegotiationStatus:Done*,metric=sum:dyn_float_USDDV01))

Individually both .es(q=NegotiationStatus:Traded,split=Book:5,metric=sum:dyn_float_USDDV01)
and
.es(q=NegotiationStatus:Done*,metric=sum:dyn_float_USDDV01)

works fine
but when I divide then the graph doesn't show anything.

Screenshot attached - The left two charts are the parts themselves. The right one is with the divide.

[ppseal]

Regarding tsvb:

I was trying I think what you are suggesting but may be I am missing something, can't do the calculation bit as the calculation isn't showing the other series - it is only showing the series I am part of.

Here is the screenshot.

I need to do the ratio of Ok vs Total and then group by any other Terms in ES like Customer Name/salesperson name etc.

[ppisljar (Peter Pisljar)]

for tsvb: you should put all of the aggregations under one metric (first agg sum, second agg sum, third agg calculation)

for timelion .,.. not sure, can you try without split ?

[ppseal]

Hi Peter,

TSVB: I need different filter for different aggs as I showed in the screenshot above. How is that possible in the same metric?

And I think that's why you suggested 4 d ago the below:

"in tsvb: create two series: use filter agg and sum metric for both of them (but with different filters)
then add calculation aggregation and divide the two previously defined seris"

Timelion: Solved that part but I can't do a divide when the target and source are bucketed by some terms.

It seems Kibana is not there yet to efficiently let calculation happen.

It seems as a result, unfortunately our trial of Kibana is not very successful and the project will look to use some other UI.