# Calculating ratio per date **URL:** \ **Category:** Kibana\ **Created:** [October 6, 2016, 11:42am UTC](https://discuss.elastic.co/t/calculating-ratio-per-date/62368 "2016-10-06T11:42:15Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![moti\_levi](https://avatars.discourse-cdn.com/v4/letter/m/dfb087/32.png) [@moti\_levi](https://discuss.elastic.co/u/moti_levi)\ **Post date:** [October 6, 2016, 11:42am UTC](https://discuss.elastic.co/t/calculating-ratio-per-date/62368/1 "2016-10-06T11:42:15Z")
Hi all, I need some help to figure out if this is something possible to do using Kibana. **this is the result table Im trying to get |o:** .................2016-10-05........2016-10-04........2016-10-03 field1.........ratio%..................ratio%..................ratio% field2.........ratio%..................ratio%..................ratio% field3.........ratio%..................ratio%..................ratio% Where the ratio is calculated s follows: the sum of each field per day, devided by the sum of the total of that field per day. for example: field1 for date 2016-10-05 should have the ratio of (100 + 110) / (150 + 150) = 210/300 = 70% and this a sample of the data: > { > "\_index": "mapping4", > "\_type": "field\_report", > "\_id": "AVeVVavr\_DKQUzatrBMb", > "\_score": 1, > "\_source": { > "field1": 100, > "field\_name": "field1", > "env": "env1", > "date": "2016-10-05T14:00:00", > "total\_counter": 150, > "customer\_global\_id": "customer1" > } > }, > { > "\_index": "mapping4", > "\_type": "field\_report", > "\_id": "AVeVVavr\_DKQUzatrBMb", > "\_score": 1, > "\_source": { > "field1": 110, > "field\_name": "field1", > "env": "env1", > "date": "2016-10-05T17:00:00", > "total\_counter": 150, > "customer\_global\_id": "customer1" > } > } > { > "\_index": "mapping4", > "\_type": "field\_report", > "\_id": "AVeVVavr\_DKQUzatrBMb", > "\_score": 1, > "\_source": { > "field1": 50, > "field\_name": "field1", > "env": "env1", > "date": "2016-10-04T14:00:00", > "total\_counter": 100, > "customer\_global\_id": "customer1" > } > }, > { > "\_index": "mapping4", > "\_type": "field\_report", > "\_id": "AVeVVavr\_DKQUzatrBMb", > "\_score": 1, > "\_source": { > "field1": 25, > "field\_name": "field1", > "env": "env1", > "date": "2016-10-05T14:00:00", > "total\_counter": 50, > "customer\_global\_id": "customer1" > } > }, > { > "\_index": "mapping4", > "\_type": "field\_report", > "\_id": "AVeVVavr\_DKQUzatrBMb1", > "\_score": 1, > "\_source": { > "field2": 100, > "field\_name": "field2", > "env": "env1", > "date": "2016-10-05T15:00:00", > "total\_counter": 150, > "customer\_global\_id": "customer1" > } > }, > { > "\_index": "mapping4", > "\_type": "field\_report", > "\_id": "AVeVVavr\_DKQUzatrBMb2", > "\_score": 1, > "\_source": { > "field2": 50, > "field\_name": "field2", > "env": "env1", > "date": "2016-10-04T15:00:00", > "total\_counter": 100, > "customer\_global\_id": "customer1" > } > }, > { > "\_index": "mapping4", > "\_type": "field\_report", > "\_id": "AVeVVavr\_DKQUzatrBMb3", > "\_score": 1, > "\_source": { > "field2": 25, > "field\_name": "field2", > "env": "env1", > "date": "2016-10-05T15:00:00", > "total\_counter": 50, > "customer\_global\_id": "customer1" > } > }, > { > "\_index": "mapping4", > "\_type": "field\_report", > "\_id": "AVeVVavr\_DKQUzatrBMb11", > "\_score": 1, > "\_source": { > "field3": 200, > "field\_name": "field3", > "env": "env1", > "date": "2016-10-05T16:00:00", > "total\_counter": 200, > "customer\_global\_id": "customer1" > } > }, > { > "\_index": "mapping4", > "\_type": "field\_report", > "\_id": "AVeVVavr\_DKQUzatrBMb12", > "\_score": 1, > "\_source": { > "field3": 250, > "field\_name": "field3", > "env": "env1", > "date": "2016-10-04T16:00:00", > "total\_counter": 300, > "customer\_global\_id": "customer1" > } > }, > { > "\_index": "mapping4", > "\_type": "field\_report", > "\_id": "AVeVVavr\_DKQUzatrBMb13", > "\_score": 1, > "\_source": { > "field3": 200, > "field\_name": "field3", > "env": "env1", > "date": "2016-10-05T16:00:00", > "total\_counter": 250, > "customer\_global\_id": "customer1" > } > } Thanks to anyone who got this far 🙂 ---
**Author:** ![LeeDr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leedr/32/9289_2.png) [@LeeDr](https://discuss.elastic.co/u/LeeDr)\ **Post date:** [October 7, 2016, 6:29pm UTC](https://discuss.elastic.co/t/calculating-ratio-per-date/62368/2 "2016-10-07T18:29:49Z")
Hi moti\_levi, I didn't find a way to do that in a Data Table. There might be a way to use the Advanced field in the Visualization to put in some JSON to get the right results. I'll try to take a look at that too. But I did figured out a way to chart it with Timelion. Timelion is a plugin in Kibana 4.x, but it's built-in to Kibana 5.0 in beta now. On my Kibana 4.6 I installed it with `/opt/kibana/bin/kibana plugin --install kibana/timelion` Here's the query I used; `.es(index=test8, timefield=date, metric='sum:field1').divide(.es(index=test8, timefield=date, metric='sum:total_counter', q='field1>0') ).multiply(100).points(radius=2) .es(index=test8, timefield=date, metric='sum:field2').divide(.es(index=test8, timefield=date, metric='sum:total_counter', q='field2>0')).multiply(100).points(radius=6) .es(index=test8, timefield=date, metric='sum:field3').divide(.es(index=test8, timefield=date, metric='sum:total_counter', q='field3>0')).multiply(100).points(radius=10)` A little explanation of the query above; `.es(index=test8, timefield=date, metric='sum:field1')` gets the field1 sum over the 1d interval I've selected in Timelion `.es(index=test8, timefield=date, metric='sum:total_counter', q='field1>0')` gets the sum of the total\_counter over the 1d interval, but only where field1\>0 otherwise it gets all the total\_counters on that day That total\_counter sum is used to divide the field1 sum. `.multiply(100)` just makes the numbers more like a percent. There might be a better way to set as a percent format. which produced this chart; ![](https://us1.discourse-cdn.com/elastic/original/2X/6/64826c3a23156f2f396016d0add5c43b5dbe5d1c.png) There's not currently a way to get the data table type of output from Timelion. Regards, Lee ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 1:37pm UTC](https://discuss.elastic.co/t/calculating-ratio-per-date/62368/3 "2017-07-06T13:37:18Z")