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.