Allow plotting on secondary y-axis

[thomaskejser]

Using SQL page for my reporting on DuckDB

I would like to create compact graphs that show data series that have different scales of numbers on different axis.

What I would like is something like this on the data:

SELECT 'graph' as component, 'Small Number' AS ytitle, 'Large Numbers' AS ytitle2;

SELECT 'foo' AS series, 1 AS x, 10 as y, 1 as yaxis 
UNION ALL
SELECT 'foo' AS series, 1 AS x, 20 as y, 1 as yaxis 
UNION ALL
SELECT 'bar' AS series, 1 AS x, 100000 as y, 2 as yaxis 
UNION ALL
SELECT 'bar' AS series, 1 AS x, 200000 as y, 2 as yaxis 

What I would now expect to happen is that the right side of the plot contains a secondary y-axis with 'Large Numbers' as the title and where the series 'bar' is plotted on that axis and series 'foo' is plotted on the first axis (titled: 'small number')

It can be an error to plot the same series on different y-axis - though that does not really have to be the case (the other choice is equally valid)

[lovasoa]

Currently, sqlpage does not support secondary y axes, and I'm not sure it should. It tends to make unrelated or weakly related series look strongly correlated, and misrepresents series dynamics.

See for reference:

• https://www.datawrapper.de/blog/dualaxis
• https://www.sciencedirect.com/science/article/abs/pii/S0360131525002763

Depending on the use casen I'd recommend

• using the card component to represent series side by side
• normalizations of the series (for instance, represent a change percentage over time)
• using a scatterplot to represent the co-evolution of two or three values

[thomaskejser]

That's a fair point (though IMHO highly subjective - Excel has been doing this for 50 years and nobody seems to mind).

If the alternative is to use two charts under each other, then one of these two features would be needed to that properly:

1. xmin/xmax so i can make sure the graphs share the same width on the x axis and can be read and compared easily

2. Sharing of x-axis, but in two different plots on top of each other. For example, one could have this:

SELECT 'chart' AS component, 'first plot' AS ytitle, 'second plot' AS ytitle ; SELECT 'plot1' AS plot, 'foo' AS series, 1 AS x, 1000 AS y UNION ALL SELECT 'plot1' AS plot, 'foo' AS series, 1 AS x, 2000 AS y UNION ALL SELECT 'plot2' AS plot, 'foo' AS series, 1 AS x, 1 AS y UNION ALL SELECT 'plot12 AS plot, 'foo' AS series, 1 AS x, 2 AS y

Cards don't really solve this does it ?

[lovasoa]

xmin and xmax should work already, but I see they are not documented ! I need to fix that

[81reap]

#1376 and #1377 allow for plotting reference lines & areas along the x axis as well as documented examples

[81reap]

seems like the linked resources have updated guidance

• https://www.datawrapper.de/blog/dual-axis-charts-guide

these could be warnings or rules we can capture in a warning system like #210 (comment)