How do I add 3 fields and get an average?

[dandcp]

I want to add 3 different fields and then divide them for an average. The fields are:

business_poc_flag
technical_poc_flag
functional_poc_flag

The value is either a "1" or a "0" (business_poc_flag = 1 if the business_poc field has a value and so on).

These fields are all tied to different records that essentially are applications names, so the data looks like this:

application 1:
portfolio: security
business_poc: Joe Blow
business_poc_flag: 1
technical_poc:
technical_poc_flag: 0
functional_poc: Jane Doe
functional_poc_flag: 1

application 2
portfolio: security
business_poc:
business_poc_flag: 0
technical_poc:
technical_poc_flag: 0
functional_poc: Jane Doe
functional_poc_flag: 1

application 3
portfolio: finance
business_poc: Joe Blow
business_poc_flag: 1
technical_poc: John Bowers
technical_poc_flag: 1
functional_poc: Jane Doe
functional_poc_flag: 1

In this scenario if I were to average out the flag fields I could use it in a heat map to color code - for example:

application 1 = 0.66
application 2 = 0.33
application 3 = 1

What I want to do is create a heat map that is sorted by portfolio and colors it based on their "completion" so if you have an average of 1 (or a sum of 3) then you would be green. If you have an average of .66 (or a sum of 2) then you would be orange. If you have an average of .33 (or a sum of 1) then you would be red, and so on.

So in this scenario the first two applications are in the security portfolio and 1 app has 2 fields with value and the other has 1, so they are running a 3/6 or 50%. I'd like to figure out a way to show this. But it means I need to be able to count the number of applications, sort them by portfolio, sum the flag fields (all 3) and then divide that by the number of fields that were counted (so if there were two apps it would be 6 fields, if there were 3 apps it would be 9 fields, and so on.

When I use filters I have tried the syntax "business_poc_flag: "1" + technical_poc_flag: "1" + functional_poc_flag: "1" but it is giving me weird answers. It seems to add two of the fields, but when I get to 3 the numbers aren't adding up. What is the syntax I should be using to do this in the filter aggregation?

I cannot use scripted fields b/c of a limitation of our deployment. I'm running on 6.8.

[AClerk]

Can you calculate the sum before loading into elastic?
Where is the data coming from? How do you process it before elastic?

[dandcp]

@Aclerk that's kind of the issue. We aren't using the full ELK stack, and our implementation is limited. We have an Enterprise Architecture tool that loads data into Kibana as part of a SaaS solution. So I have access to Kibana only (of course I can query elastic via visuals and the dev tools). The issue with calculating the field ahead of time is the manner with which the EA tool we are using works. In short, yes - I can calculate the field, but the more manipulation I propose on the source data the less benefit my team and others find in using Kibana. What kills me is I could easily do this with scripted fields (at least I think so) but our implementation doesn't support that. So for the time being I'm trying to figure out if this is possible without manipulating the data before it is indexed.

I keep reading comments about people using Visual Builder to perform these calculations, but my visual is not a "time based" visual - which confuses me. Can Visual Builder be used for non-time based visuals?

[AClerk]

You have too many limitations.
I don't know how to achieve this without scripted fields or restructuring your data.

It's interesting, though.

[dandcp]

random, and different question, but related.

In my data there are 1400 records. If I open up pretty much any visualization, but for the sake of conversation let's say it's a simple "metric" visualization, the default metric will be "count" and it will show the number of records. Is it possible to manipulate that field using json input?

In short, I want to show "count * 3" but I don't know how to reference the count field/variable?