# Visualization problem when tryin' to calculate diff between averages **URL:** \ **Category:** Kibana\ **Created:** [February 16, 2018, 6:39pm UTC](https://discuss.elastic.co/t/visualization-problem-when-tryin-to-calculate-diff-between-averages/120233 "2018-02-16T18:39:21Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![CasMeiron](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/casmeiron/32/24578_2.png) [@CasMeiron](https://discuss.elastic.co/u/CasMeiron)\ **Post date:** [February 16, 2018, 6:39pm UTC](https://discuss.elastic.co/t/visualization-problem-when-tryin-to-calculate-diff-between-averages/120233/1 "2018-02-16T18:39:21Z")
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). ![28](https://us1.discourse-cdn.com/elastic/original/3X/e/d/ed2ceb5d8cf1bc1ba7c8c9f2fe4121d8fd826175.png) Let's get 2 samples to show why Kibana visualization is somehow wrong. **Time Frame =\> 10:30** Here's the A point: ![44](https://us1.discourse-cdn.com/elastic/original/3X/d/3/d32daeb96aab97bdd696312dd5583201d5978433.png) Here's the B point: ![22](https://us1.discourse-cdn.com/elastic/original/3X/4/8/4844a2b7dc5636bc8641d101b40aa13d25b2f680.png) 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. ![40](https://us1.discourse-cdn.com/elastic/original/3X/a/6/a697c645f6e8739f6d6c24d94ff86dc560028971.png) Even worse, If we get another point, let's say 14:00. LINE A: ![20](https://us1.discourse-cdn.com/elastic/original/3X/2/6/260fe70c207e02383a8b122c92056daba76688f1.png) LINE B: ![51](https://us1.discourse-cdn.com/elastic/original/3X/1/5/15df1efab3a348b7a18c41bd38582f050eaca2b8.png) 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: ![02](https://us1.discourse-cdn.com/elastic/original/3X/2/3/23094c09780cba76d15101c2ea6cca27a0e44847.png) 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! ---
**Author:** ![lukas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukas/32/6812_2.png) [@lukas](https://discuss.elastic.co/u/lukas)\ **Post date:** [February 16, 2018, 9:26pm UTC](https://discuss.elastic.co/t/visualization-problem-when-tryin-to-calculate-diff-between-averages/120233/2 "2018-02-16T21:26:56Z")
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? ---
**Author:** ![CasMeiron](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/casmeiron/32/24578_2.png) [@CasMeiron](https://discuss.elastic.co/u/CasMeiron)\ **Post date:** [February 18, 2018, 2:34pm UTC](https://discuss.elastic.co/t/visualization-problem-when-tryin-to-calculate-diff-between-averages/120233/3 "2018-02-18T14:34:51Z")
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! ---
**Author:** ![lukas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukas/32/6812_2.png) [@lukas](https://discuss.elastic.co/u/lukas)\ **Post date:** [February 20, 2018, 5:02pm UTC](https://discuss.elastic.co/t/visualization-problem-when-tryin-to-calculate-diff-between-averages/120233/4 "2018-02-20T17:02:24Z")
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](https://github.com/elastic/kibana/issues/4707) ---
**Author:** ![CasMeiron](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/casmeiron/32/24578_2.png) [@CasMeiron](https://discuss.elastic.co/u/CasMeiron)\ **Post date:** [February 23, 2018, 1:24pm UTC](https://discuss.elastic.co/t/visualization-problem-when-tryin-to-calculate-diff-between-averages/120233/5 "2018-02-23T13:24:56Z")
Thanks, that issue had few aniversaries already, I don't think it will be implemented any time soon 😕 but thanks! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [March 23, 2018, 1:25pm UTC](https://discuss.elastic.co/t/visualization-problem-when-tryin-to-calculate-diff-between-averages/120233/6 "2018-03-23T13:25:08Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.