Sorting and calculating

[dinniut (Dinesh Prabhu Kanakaraj)]

Hi, I am new to Kibana and I have questions about few operations that I would like to hear suggestions.

I have a json with 3 fields:

field_1(date),field_2(total_time),field_3(qty)
1/1/2018 , 23 , 1
1/2/2018 , 46 , 2
1/3/2018, 5 , 3
1/4/2018, 12 , 4
1/5/2018, 100 , 4

I am trying to get a scripted field to get the following calculation done:

1. sort the data based on field_2(total_time).
2. Percentile calculation based on field_3(qty) after sorting the data
3. get max(field_2-total_time) that is less than 90% of the field_3(qty) percentile calculation.

Step 1: Sorted data:

field_1(date),field_2(total_time),field_3(qty),
1/3/2018 , 5 , 3
1/4/2018 , 12 , 4
1/1/2018, 23 , 1
1/2/2018, 46 , 2
1/5/2018, 100 , 5

Step 2: Percentile calculation:

field_1(date),field_2(total_time),field_3(qty),field_4(cusum_qty),field_5(percentile)
1/3/2018 , 5 , 3 , 3 , 0.20
1/4/2018 , 12 , 4 , 7 , 0.46
1/1/2018, 23 , 1 , 8 , 0.53
1/2/2018, 46 , 2 , 10 , 0.66
1/5/2018, 100 , 5 , 3 , 15, 1.00

Step 3: get max(field_2-total_time) that is less than 90% of the field_3(qty) percentile calculation.

In this case for the current grouping of data, my max value should be 46 which corresponds to 'less than 90%' of field_3.

I can simply do this as a formula in sql before creating the json file, but I am looking to have this formula as a scripted field in Kibana so that I can make the aggregations at different levels based on date field (Day, week, month, quarter or yearly). I do not want to create separate columns for all the different levels of aggregations that I want to see.

Thanks,
Dinesh

[Joe_Fleming]

I don't think a scripted field is what you want here. And you shouldn't have to do any of the aggregations yourself, that's what Elasticsearch is for.

I don't follow what you're trying to generate though. Are you just trying to create a table with the data in your examples?

[dinniut (Dinesh Prabhu Kanakaraj)]

I apologize for not being clear.

Imagine the data that I have in elastic search is with 3 fields (date, total_time and qty) and 5 rows.

All I want ES to do for me is to calculate the value that I mentioned in Step 3 above. In the above case , I want ES to say that - after doing all the math, the answer is 46.

What are my options in this case? Please let me know if I am still unclear.

[Joe_Fleming]

Ah, I see. You might be able to do this with a pipeline aggregation, but I'm not sure that's true. The problem is that you need to do an aggregation to get the percentile value, at which point the resulting data will only have the aggregate information instead of the specific field values. Asking for the 90% percentile (or any percentile value) for the qty field is pretty simple, but mapping that back to a value in Elasticsearch is, as far as I know, not possible.