Cohort visualization is duplicating months and showing too many months.

[jasonshah]

Issue Summary

A table whose dates are first days of the month for the past six months is rendering the cohort visualization with an extra month, and with some months duplicated.

Steps to Reproduce

Create a cohort visualization based on the attached data (Visualization type: cohort; Time Interval: monthly). Notice that the month column in the data shows January through July.
The resulting visualization shows as the attached. Notice
1.) the presence of December, which does not exist in the table, and
2.) the duplicate March and May months.

Technical details:

• Redash Version: 0.10.1+b1836
• Browser/OS: Chrome on Mac OS X 10.10.5
• How did you install Redash: Docker

Data.csv.zip

[jasonshah]

Confirmed that this is an issue in 0.11.0+b2016 as well.

[jasonshah]

This appears to be a timezone issue.
I am in CST (-6) timezone. When I look at "2016-01-01 00:00:00" as it reaches the section of cornelius.js code that renders the labels, it believes the date to be "Thu Dec 31 2015 18:00:00 GMT-0600". This then gets changed to "December 2015" in the cohort table above.
Any suggestions on how to fix?
I can fake it for now by adding "+ interval '6 hours'" to my (Postgres) queries. But obviously not a good long-term solution.

[arikfr]

@jasonshah thanks for the detailed report. Can you link to the line in cornelius.js it gets changed at?

One possible fix is to pass datetime objects with timezone set to Cornelius, so it won't change it. But I prefer to avoid that, because we're trying to stay away from making assumptions on your data.

[mtrbean]

I'm also running into the same problem. I believe https://github.com/restorando/cornelius/blob/master/src/cornelius.js#L160 is how Cornelius renders the timestamp.

[edowdy]

We just ran into the same issue. Has this has been solved yet?

[JeongsangBaek]

Cornelius.js renders date string by adding +1 date period(day, week, month). It makes some problems. Descending date is also not working. It has to be modified to read date value returned by query.

[JeongsangBaek]

I'm worried that Cornelius.js is out of management.

[rdtr]

I also hit by this issue today.

I use daily Cohort visualization and dates shown in a cohort are all one day behind compared to ones that a table (= actual query result) shows.

As @jasonshah I can hardcode some offset in my query, but our dashboard can be seen by people at multiple timezones, so it's problematic.

[susodapop]

Bumping this issue: it's still present in SaaS version c7e0990.

Here is the chart when working with my base data:

Here is my data once adjusted for my offset from UTC

A sample of my query:

SELECT datetime(cohort, "+360 minutes") AS adjusted_cohort,
       cohort,
       month_diff,
       value,
       popsize
FROM query_<my-query-id>

And a sample of my data:

adjusted_cohort	cohort	month_diff	value	popsize
2018-02-01 06:00:00	2018-02-01 00:00	0	79	2841
2018-02-01 06:00:00	2018-02-01 00:00	1	68	2841
2018-02-01 06:00:00	2018-02-01 00:00	2	38	2841
2018-02-01 06:00:00	2018-02-01 00:00	3	30	2841
2018-02-01 06:00:00	2018-02-01 00:00	4	22	2841

Notice how the incorrect chart includes January 2018 (not in the base data) and duplicates May 2018.

[kravets-levko]

@susodapop tried to reproduce this issue on master using data provided by @jasonshah and seems that visualizations works fine:

Can you please try to reproduce this bug on https://redash-preview.netlify.app/ to either confirm that it's fixed or create a demo with the bug to help me investigate it? Thank you!