Deviation based on condition

Hello Kibana users. I am pretty new to Kibana and just working on it for past 10 days. I have travel time data in Kibana for some specific corridors and i can group the corridors and aggregate travel time. thats pretty fine. But, i need to calculate deviation percentage from taget value for each corridors, is it possible in Kibana. sample data looks like this

corridor name avg.travel time
xyz 15
cxy 23

each corridor has a threshold value. so in third column i need avg. travel time -threshold/threshold value *100. i am thinking is it possible in scripted fields giving if condition and 8 corridors and 8 threshold values and deviation %.

Yeah, I think you could do this with a scripted field. It would be a little tedious to write, but it should work. I'm assuming each "corridor name" has a unique threshold, but if that's not the case, this is much easier.

The gist is that for every corridor/threshold pair, you'd have to have a conditional value, and ideally some fallback default value, which you'd use to do the (time - threshold / threshold) calculation.

You can read up on the painless syntax (which scripted fields use) here: https://www.elastic.co/guide/en/elasticsearch/painless/master/painless-getting-started.html

I don't know offhand how to do conditionals, but the math part I think is simply (doc['travel time'].value - 30) / 30, where 30 is the threshold value. So, in a mostly pseudo code fashion, your scripted field would look something like:

if (doc['corridor'].value == 'first corridor') { return (doc['travel time'].value -  30) / 30 }
if (doc['corridor'].value == 'second corridor') { return (doc['travel time'].value -  35) / 35 }
if (doc['corridor'].value == 'third corridor') { return (doc['travel time'].value -  40) / 40 }
...
return return (doc['travel time'].value -  10) / 10

That last one would be the default value if the corridor doesn't match any of the ones you've defined. Hopefully you get the idea. I'm guessing that syntax is probably close but not quite correct, check out the docs to figure out how that's supposed to be written.

Thanks for reply. I entered treshold values as a column using ? operator, but i couldn't use that value to calculate anything. I am not sure how the above calculations works? since my travel time is an aggregated value group by corridor name. If i use in scripted value field above script, then doc['travel time'] would be an individual value or aggregated? I am really missing math operations on travel time and comparisons across fields like speeds, delays and travel times.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.