Divide two counts of the same index with different filters

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?

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

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?

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.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.