Divide two counts of the same index with different filters

[TheFish]

Hi, I'm trying to divide two counts in TSVB, and I've read the related answer at Divides two sum fields in kibana? 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:

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?

[stephenb (Stephen Brown)]

What version are you on?
I ask because Lens supports filters/ KQL and formulas, perhaps it would be a better solution than TSVB.

[TheFish]

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?

[TheFish]

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.