Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Line and Area charts not working when Replace Missing Values not set to Linear Interpolated #4802

Open
bendog opened this issue Apr 18, 2017 · 28 comments · May be fixed by #10413

Comments

@bendog
Copy link

commented Apr 18, 2017

Hey,

I've noticed an issue recently, and I'm not sure if it's related to a version update of Metabase or perhaps MySQL, or not, I don't recall doing anything specific which might have caused this issue.

it seems for some reason the max and sum values returned from my query are being interpreted as null values by the line and area charts.

This only seems to effect charts where Replace Missing Values is set to Zero or Nothing.
Linear interpolated seems to work fine.

Tried across multiple browsers, both on Mac OS and iPad
Host OS: ubuntu 16.04
Base DB for Metabase is MySQL 5.6
Reporting data DB is MySQL 5.6
Metabase version: 0.22.2 .jar
Also tested with 0.23.1 .jar

img_1136
img_1137
img_1138

@bendog bendog changed the title Line and Area charts not working (possibly since version update) Line and Area charts not working when Replace Missing Values not set to Linear Interpolated Apr 18, 2017

@bendog

This comment has been minimized.

Copy link
Author

commented Apr 18, 2017

This seems related to #4277, however this issue is still occurring after upgrade to latest version?

@mazameli

This comment has been minimized.

Copy link
Contributor

commented Apr 26, 2017

I was able to reproduce this.

@mfpinhal

This comment has been minimized.

Copy link

commented Jun 3, 2017

This is happening to us as well in many different cases. Another example:

Replace missing values with: Linear interpolated:
image

Replace missing values with: Zero:
image

@bendog

This comment has been minimized.

Copy link
Author

commented Jun 4, 2017

@mfpinhal it looks like that is working as expected, as you are getting one result of 1.0 and the rest are 0?
can you show the raw tables?

@mfpinhal

This comment has been minimized.

Copy link

commented Jun 6, 2017

@bendog, indeed the raw tables return values! The raw table that feeds the charts above:

@bendog

This comment has been minimized.

Copy link
Author

commented Jun 14, 2017

that looks very unusual, so there should be results, however only the first one is showing?

@vitorbaptista

This comment has been minimized.

Copy link

commented Jun 14, 2017

I'm seeing the same issue on Metabase v0.24.1

@mfpinhal

This comment has been minimized.

Copy link

commented Jun 23, 2017

that looks very unusual, so there should be results, however only the first one is showing?

This happens in many many cases, can we prioritize this one up?

Replace missing values with: Linear interpolated:

Replace missing values with: Zero:

@jrpeck1989

This comment has been minimized.

Copy link

commented Jun 29, 2017

I have this issue on 0.24.2 as well

@doodlebro

This comment has been minimized.

Copy link

commented Jun 29, 2017

This have priority? Seems like a pretty large oversight.

@ricardojmendez

This comment has been minimized.

Copy link

commented Jul 17, 2017

Confirmed that this is happening with:

  • A line graph showing the count of rows by date, where not every date has a value;
  • Data coming from Postgresql (although it looks to be purely a visualization bug);
  • Metabase v0.24.2

I've set it to show marker points to evaluate how it acts. The behavior is:

  • The data is displayed correctly as long as it is Linear Interpolated;
  • If I switch it to replace missing values with nothing, the graph is empty;
  • If I set it to replace the missing values with zero, every day will have a zero value - even those days that did not originally include a data point.

@attekei attekei assigned attekei and unassigned attekei Jul 17, 2017

@attekei

This comment has been minimized.

Copy link
Contributor

commented Aug 14, 2017

I was able to reproduce this reliably on our hosted test Metabase instance but couldn't reproduce it locally in my development environment even though both instances run the same Metabase version, both are backed by PostgreSQL database and both access the same PostgreSQL dataset (our internal Pies test dataset). I also checked the response of /api/dataset but couldn't find differences that explain the different behavior.

I wonder if this has something to with settings or timezones or if I simply missed something important.

(I originally tried to reproduce this three weeks ago but forgot to write notes here at that time)

@camsaul

This comment has been minimized.

Copy link
Member

commented Oct 20, 2017

I'm also not able to reproduce this locally or on our hosted test instance.

In fact, both "zero" and "linear interpolation" both appear to be doing the same thing for me, which is filling all then NULL values with zeroes. I used the following SQL to generate some data to test this (Postgres):

SELECT '2017-10-01'::date AS d, 3 AS c
UNION
SELECT '2017-10-02'::date AS d, 4 AS c
UNION
SELECT '2017-10-03'::date AS d, NULL AS c
UNION
SELECT '2017-10-04'::date AS d, 2 AS c
UNION
SELECT '2017-10-05'::date AS d, 4 AS c
UNION
SELECT '2017-10-06'::date AS d, NULL AS c
UNION
SELECT '2017-10-07'::date AS d, 9 AS c
UNION
SELECT '2017-10-08'::date AS d, 6 AS c

Can anyone here confirm this issue is still happening on Metabase >= 0.26.1 and, if so, give me step-by-step instructions for how to repro? SQL for some fake data like what I posted would be greatly appreciated.

@camsaul camsaul self-assigned this Oct 20, 2017

@camsaul camsaul added this to the 0.27 milestone Oct 20, 2017

@camsaul

This comment has been minimized.

Copy link
Member

commented Oct 26, 2017

Anyone? Bump

@mfpinhal

This comment has been minimized.

Copy link

commented Oct 26, 2017

Anyone? Bump

Still happenning on 0.26.1:

Linear interpolated:
image

Replace missing values with Zero:
image

@camsaul

This comment has been minimized.

Copy link
Member

commented Oct 26, 2017

@mfpinhal can you show me what that data looks like as a table? Just a few rows or so should do

@mfpinhal

This comment has been minimized.

Copy link

commented Oct 28, 2017

@camsaul, looking deeper, I think I found something else, notice the dates on the chart, they don't match the values displayed, and same weeks label are repeated:

Replace with 0's:
image
image
image

And now, the table values, perfectly normal:
image

Although the same week dates/labels are displayed, with linear interpolated option, values come through ok though:
image

Anyway, I hope this points you in the right direction to understand and fix this bug.

@mfpinhal

This comment has been minimized.

Copy link

commented Oct 28, 2017

Probably related to #5916 (actually, both cases refer to the same Metabase instance).

@arasmussen

This comment has been minimized.

Copy link

commented Aug 7, 2018

Hmm, I think I'm still seeing this issue in the 0.30 RC.

image
image

cc @tlrobinson

@rylonian

This comment has been minimized.

Copy link

commented Oct 1, 2018

I'm still seeing this issue in V0.30.4.
I have tried multiple browsers and the results are the same. Linear Interpolated works fine, but using zero or nothing sets all the x axis points in the graph to 0 or undefined.

Host OS: ubuntu 18.04
Base DB for Metabase is MySQL 5.7.23
Reporting data DB is MS SQL Server 2014 - 12.0.2269.0
Metabase version: 0.30.4.jar

Strangely, I have tested metabase on my local Windows 10 PC and it works fine there. My windows PC just has Java 8 and I used the database created by metabase. I hope this helps.

Thanks!

image

image

@rylonian

This comment has been minimized.

Copy link

commented Oct 1, 2018

I did some more testing for this specific issue. I have tested it on Ubuntu 18.04, Windows 10, and Server 2012 R2. Ubuntu is the only operating system where changing the graph to use "Zero" or "Nothing" instead of "Linear Interpolated" causes the X axis to break. I don't know why Ubuntu would work differently than Windows, but there is something.

I have tested this with my personal data as well as the default sample data and I still achieve the same results.

To reproduce:

  1. Install a fresh instance of metabase on Ubuntu and run it.
  2. Setup account / whatever is needed just for the default sample data.
  3. Ask a Question
  4. Choose Custom with the following options:
    Data: Orders
    Filtered By: Created At Past 30 Days
    View: Sum of Total
    Grouped By: Created At: Day
  5. Click Get Answer
  6. Change the Visualization to a Line graph
  7. Click the Gear icon -> Display and change the Replace missing values with option to Zero.

I was hoping to be able to run this on a Linux VM instead of Windows. If anyone has a work around, please let me know. I will be happy to test or provide whatever information is needed.

Thanks again!

@rylonian

This comment has been minimized.

Copy link

commented Oct 1, 2018

Final Comment for now.
This seems to be related to the server being set to the UTC timezone. Once I changed the timezone on the server to my local timezone and restarted Metabase, the graphs now work as expected.

Thanks again!

@mfpinhal

This comment has been minimized.

Copy link

commented Oct 2, 2018

@rylonian commented:

This seems to be related to the server being set to the UTC timezone.

Indeed, I confirm my instance is set to the UTC timezone.

@listentojohan

This comment has been minimized.

Copy link

commented Oct 18, 2018

I just encountered the same issue. (utc timestamp fields)

@ValdarT

This comment has been minimized.

Copy link

commented Nov 10, 2018

Still there on v0.31.0. Highly problematic. :(

@keshunchen

This comment has been minimized.

Copy link

commented Dec 2, 2018

I'm still seeing this issue in V0.31.1

@sthornock

This comment has been minimized.

Copy link

commented Dec 6, 2018

So I think it is related to this..
If my x-axis is a "date/time" then it only interpolated values work for missing values.
If I change the x-axis column to be something like this...in SQL server
convert(varchar(25), [Date], 101) As PlanDate then it works.

Try converting your x-axis column to a varchar and format it as a work around.

@tuliosciotta

This comment has been minimized.

Copy link

commented Mar 28, 2019

I'm still seeing this issue in V0.31.2...

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
You can’t perform that action at this time.