# Divide two counts of the same index with different filters **URL:** \ **Category:** Kibana\ **Created:** [January 20, 2023, 9:44pm UTC](https://discuss.elastic.co/t/divide-two-counts-of-the-same-index-with-different-filters/323631 "2023-01-20T21:44:00Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![TheFish](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/thefish/32/116225_2.png) [@TheFish](https://discuss.elastic.co/u/TheFish)\ **Post date:** [January 20, 2023, 9:44pm UTC](https://discuss.elastic.co/t/divide-two-counts-of-the-same-index-with-different-filters/323631/1 "2023-01-20T21:44:00Z")
Hi, I'm trying to divide two counts in TSVB, and I've read the related answer at [Divides two sum fields in kibana?](https://discuss.elastic.co/t/divides-two-sum-fields-in-kibana/217386) but I have a twist, and I can't get it to work: I have one index with "death" events in a game. Each event has the attacking player name, the killed player name, and an "is\_kill" field that's a boolean. This boolean is true whenever the attacking player is one of the players of our group and this makes it easy to do global kill/death counts for the group. What I'm trying to do is to calculate a list of the players with the highest K/D ratio. So I want for each player a bar that shows the result of count(is\_kill: true) / count(is\_kill: false) My problem is that I can't a count of filtered documents as an aggregation. I've been trying something like this but it's always 1: ![image](https://us1.discourse-cdn.com/elastic/original/3X/0/f/0fd6f930f6fd6fa9161511330e3351ea57d8eff2.png) I can do the "count" aggregation but that one doesn't do any filters. So I guess my question is: How can I do such an aggregation but with a filter just for that aggregation? ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [January 21, 2023, 12:12am UTC](https://discuss.elastic.co/t/divide-two-counts-of-the-same-index-with-different-filters/323631/2 "2023-01-21T00:12:38Z")
What version are you on? I ask because Lens supports filters/ KQL and formulas, perhaps it would be a better solution than TSVB. ---
**Author:** ![TheFish](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/thefish/32/116225_2.png) [@TheFish](https://discuss.elastic.co/u/TheFish)\ **Post date:** [January 21, 2023, 9:07am UTC](https://discuss.elastic.co/t/divide-two-counts-of-the-same-index-with-different-filters/323631/3 "2023-01-21T09:07:58Z")
Heya! I'm on version 7.13.4 I was looking at lens but I didn't find a way to enter formulae. Also to maybe explain this a bit simpler, what I'd like to do is a graph for this: horizontal: top values vertical: count(filter=is\_kill:true) / count(filter=is\_kill:false) Break down by: playername EDIT: I just upgrade to v8 and have tried the following formula in lens for the vertical axis: count(kql='is\_kill : true') / count(kql='is\_kill : false') However, all I get is an empty graph. Am I missing something? ---
**Author:** ![TheFish](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/thefish/32/116225_2.png) [@TheFish](https://discuss.elastic.co/u/TheFish)\ **Post date:** [January 21, 2023, 12:00pm UTC](https://discuss.elastic.co/t/divide-two-counts-of-the-same-index-with-different-filters/323631/4 "2023-01-21T12:00:32Z")
Silly me, turns out that for K/D calculation you can have situations where players have 5 kills but no deaths, which would be a division by zero. So to solve that I use the following: count(kql='is\_kill : true') / pick\_max(count(kql='is\_kill : false'),1) This means for 5/0 the K/D is 5, for 5/1 the K/D is also 5, and for 5/2 the K/D is 2.5 Now the last problem was: I want to show the top K/D players in a bar chart. To do this, I used playername for the horizontal axis, rank by CUSTOM, rank function COUNT. I'm still testing this but I _think_ this should be it. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 18, 2023, 12:00pm UTC](https://discuss.elastic.co/t/divide-two-counts-of-the-same-index-with-different-filters/323631/5 "2023-02-18T12:00:40Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.