Visualization problem when tryin' to calculate diff between averages

[CasMeiron (Paulo Reis)]

Hello,

I think I'm doing something pretty wrong or there's indeed a bug in visualization.

I will try to do my best to describe my case.

I have 2 fields: A,B.

So these fields are plotted (average) using LINE in my visualization. Then, I've added another LINE (field A, AVERAGE) using a SCRIPT that does the follow:

If (A-B) > 0 then use '0' otherwise use (A-B).

SCRIPT:
{ "script" : "(_value - doc['B'].value) > 0 ? 0: (_value - doc['B'].value) " }

Ok so I expect to see the value 0 when the diff between A and B is greater than 0. The problem is that I never see 0, even though the expression enters the IF.

Follow my visualization with 3 LINES (A,B,DIFF).

Let's get 2 samples to show why Kibana visualization is somehow wrong.

Time Frame => 10:30

Here's the A point:

Here's the B point:

So if we do the math: A-B = 705,191186. So as this value is greater than 0, I expect to see in the visualization the time 10:30 (in the third line, DIFF) showing a point in 0 but that's not what happened.

Even worse, If we get another point, let's say 14:00.

LINE A:

LINE B:

The diff between A-B will be 476,648437 which is greater than 0 so it should plot (in the third line, DIFF) the value 0 but let's see what kibana did:

How could it be -239.291797?

It seems to me that the 3 lines are somehow disconnected from each ether, like running in different time window, even though they're tied together with a single X axis. Maybe I'm doing something wrong. Could anybody help me with that one? I have no idea what's going on.

Btw, in TimeLion it works as expected.

Thanks in advance!

[lukas (Lukas Olson)]

The problem here is that your script is running on each document, rather than on the aggregated values.

Suppose you had three documents:

A | B
1 | 2
5 | 1
4 | 2

What you might expect is that the average "diff" here would be ((1 - 2) + (5 - 1) + (4 - 2)) / 3 = 1.67. However, the "diff" is calculated on each document, not on the aggregate values, so what you end up getting is this:

A | B | Diff
1 | 2 | -1
5 | 1 | 0
4 | 2 | 0

Thus, the average "diff" here will actually be -0.33.

Does that make sense?

[CasMeiron (Paulo Reis)]

I understand your point but why timelion works as expected?

.es(index ='idx', metric='avg:A', timefield='event_date').subtract(.es(index =idx, metric='avg:B')).if(gt, 0, 0).label("A").lines(fill=1,width=0.5).color("red").title('Visualization x')

Is there a way to make it work like I intended?
Like running the script on the aggregated values instead of each document? Or maybe using script field? How could I get the desired result in visualization?

Thanks in advance!

[lukas (Lukas Olson)]

Timelion works as expected because it actually is calculating the diff on the aggregate values.

There's not really a great way to accomplish this right now since we don't support bucket script aggregations. Here's the related GitHub issue: https://github.com/elastic/kibana/issues/4707

[CasMeiron (Paulo Reis)]

Thanks, that issue had few aniversaries already, I don't think it will be implemented any time soon but thanks!