Datetime visualization with missing weeks

Support
#1

I have an interesting case here - let assume a table like this (values changed to not give you my actual data):

Week Count
2015-30 7
2015-34 15
2015-35 8
2015-37 11
2015-51 1
2015-52 30
2015-53 13
2016-03 2
2016-04 17
2016-05 3

You’ll see some of those weeks are missing in my result because they had a 0 count of database items.

I’m doing a visualization of that, a bar chart with weeks as X, count as Y.

Now, if I put the X Axis into “datetime” scale (default), I only get the bars for the last 3 weeks in the set, as those are contiguous, but I’m missing all the data for weeks before the last “hole” in there. If I put the X Axis on category scale, it works, but when I get a lot of weeks in there, this will start looking weird, I’m sure - and it would be much nicer to have the X axis list dates.

#2

I start to understand, it’s not the missing ones, it’s that it interprets this as months and throws away all the ones that don’t look like valid months.

Would be really nice if a datetime-style axis mode with ISO weeks could be done, is that planned?

#3

For the chart visualization we depend on Plotly.js. You can check with them if this is something planned. I did see someone’s pull request there that improves dates support, so maybe this will be part of it.

Until then you can either translate those to dates (have the first day of the week instead of week number?) or change the X Axis to “Category” type.

#4

Yes, I’m using “Category” for now, though of course that ignores missing weeks completely. From what I saw in Plotly.js code, they don’t support weeks right now at all (I tried to find out if they had some format that would work to get weeks in), also that PR doesn’t add it and there does not be an open issue about supporting weeks there. I guess I’ll stick with either category or converting to start-of-week dates. That said, the requests to me to do visualizations with “weekly” dimensions are high, and I guess I’m not alone with this, so it would be good to get something there in the long run.

#5

So, just for anyone reading this, I found that start-of-week dates work pretty decently, actually.
I had been doing |to_char(db_date, ‘IYYY-IW’) AS week| before (PostgreSQL as a backend) but I now tried |date_trunc(‘week’, db_date) AS week| and the visualizations come out looking pretty nice.