# Create custom metric for Y-axis, dealing with no existed values **URL:** \ **Category:** Kibana\ **Created:** [October 25, 2017, 10:58am UTC](https://discuss.elastic.co/t/create-custom-metric-for-y-axis-dealing-with-no-existed-values/105222 "2017-10-25T10:58:35Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![lsouvleros](https://avatars.discourse-cdn.com/v4/letter/l/d2c977/32.png) [@lsouvleros](https://discuss.elastic.co/u/lsouvleros)\ **Post date:** [October 25, 2017, 10:58am UTC](https://discuss.elastic.co/t/create-custom-metric-for-y-axis-dealing-with-no-existed-values/105222/1 "2017-10-25T10:58:35Z")
Hello, I just started to use ELK and I want your help with issues. I have a dataset with the following fields Username,Name,Recruitment\_date,Recruitment\_year,duration\_at\_company,leave\_date, leave\_year and I want to find the number of employees for each year. So we want for year in years\_set: ``` Recruitment_year<=year and leave_year >= year ``` My first issue is how I can write something like the above command to kibana. It is easy for a given year using a static query but I want it for every year. My second issue is that I wonder if there is any way to deal with dates or generally values of dates that don't exist. For example,at the following diagram I want to find the average duration of employees per year of recruitment. But at 2011 there isn't any recruitment and we have a continues line from 2010 to 2012. Instead I would prefer to have a zero value for 2011. ---
**Author:** ![weltenwort](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/weltenwort/32/53885_2.png) [@weltenwort](https://discuss.elastic.co/u/weltenwort)\ **Post date:** [October 25, 2017, 2:46pm UTC](https://discuss.elastic.co/t/create-custom-metric-for-y-axis-dealing-with-no-existed-values/105222/2 "2017-10-25T14:46:55Z")
Hi @lsouvleros, it would be helpful to know whether you want to visualize the data using Kibana's visualizations or extract them as JSON via Elasticsearch's http API and use them in another tool. I will assume the former for my answers. For your first question I would suggest to add a scripted field of type `number` called `employed_years` to your index defined as ``` IntStream.range(doc['Recruitment_year'].value, doc['leave_year'].value).toArray() ``` which holds all the years that the person was an employee. Then you can use that fields e.g. in `histogram` or `terms` aggregations to get the number of employees. I will get to your second question in a short while in a follow-up post. 😉 ---
**Author:** ![weltenwort](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/weltenwort/32/53885_2.png) [@weltenwort](https://discuss.elastic.co/u/weltenwort)\ **Post date:** [October 25, 2017, 5:56pm UTC](https://discuss.elastic.co/t/create-custom-metric-for-y-axis-dealing-with-no-existed-values/105222/3 "2017-10-25T17:56:27Z")
The wording of your last paragraph suggested that you intended to show an example of the diagram. That might indeed be useful to help my understanding of your problem. ---
**Author:** ![lsouvleros](https://avatars.discourse-cdn.com/v4/letter/l/d2c977/32.png) [@lsouvleros](https://discuss.elastic.co/u/lsouvleros)\ **Post date:** [October 25, 2017, 10:27pm UTC](https://discuss.elastic.co/t/create-custom-metric-for-y-axis-dealing-with-no-existed-values/105222/4 "2017-10-25T22:27:53Z")
Look for example the following picture. For 2010 and 2012 I have some values which give me information for these years, but for 2011 I don't have any value/record. So I would like the value of 2011 to be 0. Instead there is no value for 2011 and the line just piece together the values of 2010 and 2012. ![Screenshot from 2017-10-25 12-33-24](https://us1.discourse-cdn.com/elastic/original/3X/6/c/6cebcfb735e771ea44382bac73f65193454ab27b.png) ---
**Author:** ![lsouvleros](https://avatars.discourse-cdn.com/v4/letter/l/d2c977/32.png) [@lsouvleros](https://discuss.elastic.co/u/lsouvleros)\ **Post date:** [October 26, 2017, 8:12am UTC](https://discuss.elastic.co/t/create-custom-metric-for-y-axis-dealing-with-no-existed-values/105222/5 "2017-10-26T08:12:57Z")
Hi @weltenwort, Thanks you for your help. I want to visualize the data using Kibana's visualization. At a next stage, could I use a scripted field again to calculate the following number\_of\_recruitments\_per\_year / total\_number\_of\_employees\_per year ? ---
**Author:** ![weltenwort](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/weltenwort/32/53885_2.png) [@weltenwort](https://discuss.elastic.co/u/weltenwort)\ **Post date:** [October 26, 2017, 9:12am UTC](https://discuss.elastic.co/t/create-custom-metric-for-y-axis-dealing-with-no-existed-values/105222/6 "2017-10-26T09:12:41Z")
Ok, I see what you mean in regards to missing values. By default Elasticsearch leaves out empty buckets and Kibana's line visualization just connects the non-empty buckets. If you are using a `Histogram` or `Date Histogram` aggregation on the x-axis you can force Elasticsearch to also return empty buckets in between by specifying `{"min_doc_count" : 0}` in the advanced `JSON Input` field of the aggregation: ![image](https://us1.discourse-cdn.com/elastic/original/3X/d/c/dcf9293100829e187247a8209f01687e51d5027f.png) Since the data are not continuous anyway, disabling the line and just using the dots or switching to a vertical bar chart might also be appropriate here. Regarding the ratio: Scripted fields can only access data from a single document at a time. It is therefore not possible to use them to calculate such a ratio. By combining several pipeline aggregations it should be possible to let Elasticsearch calculate that, but those aggregations are unfortunately not yet supported by the visualization editor. ---
**Author:** ![lsouvleros](https://avatars.discourse-cdn.com/v4/letter/l/d2c977/32.png) [@lsouvleros](https://discuss.elastic.co/u/lsouvleros)\ **Post date:** [October 26, 2017, 9:20am UTC](https://discuss.elastic.co/t/create-custom-metric-for-y-axis-dealing-with-no-existed-values/105222/7 "2017-10-26T09:20:47Z")
> [@weltenwort](#): > > By combining several pipeline aggregations it should be possible to let Elasticsearch calculate that, but those aggregations are unfortunately not yet supported by the visualization editor. So there is no solution or trick to calculate it at the moment? ---
**Author:** ![lsouvleros](https://avatars.discourse-cdn.com/v4/letter/l/d2c977/32.png) [@lsouvleros](https://discuss.elastic.co/u/lsouvleros)\ **Post date:** [October 26, 2017, 11:53am UTC](https://discuss.elastic.co/t/create-custom-metric-for-y-axis-dealing-with-no-existed-values/105222/8 "2017-10-26T11:53:19Z")
> [@weltenwort](#): > > IntStream.range(doc['Recruitment\_year'].value, doc['leave\_year'].value).toArray() I am getting this error Unrecognized function call (IntStream.range),"status":500 Moreover, there is type Array to Kibana. What type of data will it save? How will be their format? ---
**Author:** ![weltenwort](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/weltenwort/32/53885_2.png) [@weltenwort](https://discuss.elastic.co/u/weltenwort)\ **Post date:** [October 26, 2017, 12:12pm UTC](https://discuss.elastic.co/t/create-custom-metric-for-y-axis-dealing-with-no-existed-values/105222/9 "2017-10-26T12:12:02Z")
Which version of the Elastic Stack are you using? ---
**Author:** ![lsouvleros](https://avatars.discourse-cdn.com/v4/letter/l/d2c977/32.png) [@lsouvleros](https://discuss.elastic.co/u/lsouvleros)\ **Post date:** [October 26, 2017, 12:14pm UTC](https://discuss.elastic.co/t/create-custom-metric-for-y-axis-dealing-with-no-existed-values/105222/10 "2017-10-26T12:14:25Z")
5.6.3 ---
**Author:** ![lsouvleros](https://avatars.discourse-cdn.com/v4/letter/l/d2c977/32.png) [@lsouvleros](https://discuss.elastic.co/u/lsouvleros)\ **Post date:** [October 27, 2017, 8:27am UTC](https://discuss.elastic.co/t/create-custom-metric-for-y-axis-dealing-with-no-existed-values/105222/11 "2017-10-27T08:27:19Z")
This worked changing Int to Long LongStream.range(doc['recruitment\_year'].value, doc['leave\_year'].value).toArray() It was a false error. Thanks you a lot for your help. If you can suggest any way to visualize the ratio I would be grateful. ---
**Author:** ![weltenwort](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/weltenwort/32/53885_2.png) [@weltenwort](https://discuss.elastic.co/u/weltenwort)\ **Post date:** [October 27, 2017, 9:43am UTC](https://discuss.elastic.co/t/create-custom-metric-for-y-axis-dealing-with-no-existed-values/105222/12 "2017-10-27T09:43:21Z")
I was able to produce something that might match your requirements using timelion. It contains both graphs in one, but could obviously also be separated: ![image](https://us1.discourse-cdn.com/elastic/original/3X/e/3/e336d2122861695545e8f88873917d147e50d527.png) It could even display a higher accuracy than just years (if your dates are precise enough): ![image](https://us1.discourse-cdn.com/elastic/original/3X/e/2/e216ca768363fe1fa1e4c0c93449b296c85a1fed.png) The timelion expression for that was ``` $INDEX="employee", $START_FIELD="recruitment_date", $END_FIELD="leave_date", .es(index=$INDEX, timefield=$START_FIELD).subtract(.es(index=$INDEX, timefield=$END_FIELD)).cusum().label("employee count").lines(width=1, fill=3, steps=true), .es(index=$INDEX, timefield=$START_FIELD).divide(.es(index=$INDEX, timefield=$START_FIELD).subtract(.es(index=$INDEX, timefield=$END_FIELD)).cusum()).label("recruitment/employee ratio").lines(width=1, fill=7, steps=true) ``` This doesn't depend on the scripted fields we defined earlier but uses timelion's ad-hoc math capabilities. ---
**Author:** ![weltenwort](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/weltenwort/32/53885_2.png) [@weltenwort](https://discuss.elastic.co/u/weltenwort)\ **Post date:** [October 27, 2017, 9:47am UTC](https://discuss.elastic.co/t/create-custom-metric-for-y-axis-dealing-with-no-existed-values/105222/13 "2017-10-27T09:47:39Z")
To make the ratio more readable, it could also be placed on the right-hand axis: ![image](https://us1.discourse-cdn.com/elastic/original/3X/5/c/5c19e64f92e64182e006234c487d65e7a0729753.png) ``` $INDEX="employee", $START_FIELD="recruitment_date", $END_FIELD="leave_date", .es(index=$INDEX, timefield=$START_FIELD).subtract(.es(index=$INDEX, timefield=$END_FIELD)).cusum().label("employee count").lines(width=1, fill=3, steps=true), .es(index=$INDEX, timefield=$START_FIELD).divide(.es(index=$INDEX, timefield=$START_FIELD).subtract(.es(index=$INDEX, timefield=$END_FIELD)).cusum()).label("recruitment/employee ratio").lines(width=1, fill=7, steps=true).yaxis(yaxis=2, units="percent", max=2) ``` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [November 24, 2017, 9:47am UTC](https://discuss.elastic.co/t/create-custom-metric-for-y-axis-dealing-with-no-existed-values/105222/14 "2017-11-24T09:47:43Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.