Customising xlabels on charts

[accforgithubtest]

When trying to display a line chart with time-series data (about 200 days, one record per day), The chart is rendered as below. Is there any way to customize the label on the x-axis to display only the date and month as label (detailed timestamp is displayed on hover anyways).

I reduced the clutter by specifing a low value for "xticks", that has reduced the clutter by not displaying the timestamp label for each day. However it would be nice to be able to do some kind of formatting to make the timestamp labels on x look nicer.

Any formatting I can do to the timestamps would result in messing with actual data. / graph not being displayed

While looking at the documentation, i see a Row-level parameters option "label", to which I tried passing date_format(timestamp, '%d %b'), but that doesnt work. It looks like the label is just another name for X - which I am not sure how to use it.

Is there a way to specify the time series column as the x values, and another formatted column ( for example date_format(timestamp, '%d %b') ) as the x label ? Am I thinking in the correct direction ?

Any suggestions is appreciated. Thanks !

[lovasoa]

Hello and welcome to SQLPage ! Out of curiosity, what is it that you are building ?

I am currently working on improving time series support in the chart component, and it should be much better in v0.14, without having to resort to any trickery. Here is a preview of the new time top-level parameter:

It will be released soon, but in the meantime, you have to do the formatting in SQL directly on the x value, in a way that preserves ordering. If you have an example where this breaks the graph and you think it shouldn't, you can post reproduction information here (database used, dataset, exact sql queries) and I'll look into it !

[lovasoa]

I just published v0.14.0 with improved support for time series.

[accforgithubtest]

wow, that was quite the coincidental timing ! And thank you. I think this project is a good idea to get a webpage just out of sql's.

I am trying to test out a few simple examples with sql page for now, before I can decide how I can use sqlpage. Hoping if I can use sqlpage instead of metabase. The example above is of simple FX timeseries data over the past few months.

I got 0.14 version, and all I added was the below 2 lines, and the x axis labels looks so much better now as shown above in your screen shot. That is very neat, thank you so much !

$ as marker,
TRUE as time;

Another question I have is about support for multiple lines in the same chart. Looking at the docs, I assume all I have to do is add TRUE as stacked to the chart component, and then have this in the select query as the 3 columns

       dateYyyymmdd as x,
    amount as y,
    account as series

Currently the chart is blank, and on hover I see NaN as the y values through out. (same query works fine as I can see the data iseperately as well as with metabase btw).

I cannot share the data as-is because of work reasons. I will need to probably see how I can prepare some test data to work with for examples so I can share for issues discussion.

What would be the best way to share some [larger] timeseries data csv examples and then also share the sqlpage sql files that go with it ?

[lovasoa]

Great to hear that it works for you !

Side note: 5 as marker, is not mandatory, and may not be ideal if you have lots of data. It adds markers (a little bubble for each data point).

I'll add an example with multiple time series to the official website. Multiple series should indeed use the series row-level attribute.

The best way to share a small SQLPage reproduction, while waiting for SQLPage cloud, it to use the SQLPage repl.it template.

[accforgithubtest]

Thanks, will wait for your example for multiple series ! In the mean time, will try repl.it. Thanks for the replies and help !