search

lovasoa / SQLpage

SQL-only webapp builder, empowering data analysts to build websites and applications quickly
https://sql.ophir.dev
MIT License
896 stars 63 forks source link

Behavior of Stacked Bar chart changed in v0.18.2 #210

Open accforgithubtest opened 5 months ago

accforgithubtest commented 5 months ago

Behavior of Stacked Bar chart changed in v0.18.2, when compared to all previous versions (v0.12 until v0.17.1).

I think in all previous versions, the "stacked" was actually doing a "overlay" of the bars, which was very useful to visualize totals, and breakdowns as can be seen in the below example. Also importantly, the bars aligned with the y-axis scale correctly.

In v0.18.2, the bars are stacked vertically, meaning the same charts does not make sense as the totals,as well as the breakdowns (cat-a, cat-b) do not align with the scale of the y-axis. And also the same bar chart doesn't make as much sense visually (I think).

I have few questions -

  1. Should the "stacked" behavior be what it is in v0.18.2 ? Or should it be what it used to be until v0.17.1 ?
  2. Any options to get the same behaviour of v0.17.1 in v0.18.2 ?
  3. Can there please be a "overlay" feature which would restore the behavior similar to v0.17.1 ? This is actually a very good visualization as the numbers actually lines up with the y-axis scale.

v0.17.1 image

v0.18.2 image

Test Data - 

select 'shell' as component,
    'dark' as theme,
    'Test Page' as title,
    '#' as link,
    'en-AU' as language;

select 'chart' as component,
    'Bar Chart Test' as title,
    'bar' as type,
    TRUE as stacked,
    TRUE as toolbar,
    TRUE as time;

select '2020-01' as x,  '30' as y,  'Total' as series
select '2020-01' as x,  '20' as y,  'Cat-a' as series
select '2020-01' as x,  '10' as y,  'Cat-b' as series

select '2020-02' as x,  '27' as y,  'Total' as series
select '2020-02' as x,  '15' as y,  'Cat-a' as series
select '2020-02' as x,  '12' as y,  'Cat-b' as series

select '2020-03' as x,  '27' as y,  'Total' as series
select '2020-03' as x,  '15' as y,  'Cat-a' as series
select '2020-03' as x,  '12' as y,  'Cat-b' as series

select '2020-04' as x,  '60' as y,  'Total' as series
select '2020-04' as x,  '40' as y,  'Cat-a' as series
select '2020-04' as x,  '20' as y,  'Cat-b' as series

select '2020-05' as x,  '30' as y,  'Total' as series
select '2020-05' as x,  '20' as y,  'Cat-a' as series
select '2020-05' as x,  '10' as y,  'Cat-b' as series

select '2020-06' as x,  '60' as y,  'Total' as series
select '2020-06' as x,  '40' as y,  'Cat-a' as series
select '2020-06' as x,  '20' as y,  'Cat-b' as series

select '2020-07' as x,  '45' as y,  'Total' as series
select '2020-07' as x,  '25' as y,  'Cat-a' as series
select '2020-07' as x,  '20' as y,  'Cat-b' as series

select '2020-08' as x,  '30' as y,  'Total' as series
select '2020-08' as x,  '20' as y,  'Cat-a' as series
select '2020-08' as x,  '10' as y,  'Cat-b' as series

select '2020-09' as x,  '27' as y,  'Total' as series
select '2020-09' as x,  '15' as y,  'Cat-a' as series
select '2020-09' as x,  '12' as y,  'Cat-b' as series

select '2020-10' as x,  '45' as y,  'Total' as series
select '2020-10' as x,  '25' as y,  'Cat-a' as series
select '2020-10' as x,  '20' as y,  'Cat-b' as series

select '2020-11' as x,  '45' as y,  'Total' as series
select '2020-11' as x,  '25' as y,  'Cat-a' as series
select '2020-11' as x,  '20' as y,  'Cat-b' as series

select '2020-12' as x,  '60' as y,  'Total' as series
select '2020-12' as x,  '40' as y,  'Cat-a' as series
select '2020-12' as x,  '20' as y,  'Cat-b' as series

select '2021-01' as x,  '45' as y,  'Total' as series
select '2021-01' as x,  '25' as y,  'Cat-a' as series
select '2021-01' as x,  '20' as y,  'Cat-b' as series

select '2021-02' as x,  '45' as y,  'Total' as series
select '2021-02' as x,  '25' as y,  'Cat-a' as series
select '2021-02' as x,  '20' as y,  'Cat-b' as series

select '2021-03' as x,  '27' as y,  'Total' as series
select '2021-03' as x,  '15' as y,  'Cat-a' as series
select '2021-03' as x,  '12' as y,  'Cat-b' as series

select '2021-04' as x,  '30' as y,  'Total' as series
select '2021-04' as x,  '20' as y,  'Cat-a' as series
select '2021-04' as x,  '10' as y,  'Cat-b' as series

select '2021-05' as x,  '27' as y,  'Total' as series
select '2021-05' as x,  '15' as y,  'Cat-a' as series
select '2021-05' as x,  '12' as y,  'Cat-b' as series

select '2021-06' as x,  '45' as y,  'Total' as series
select '2021-06' as x,  '25' as y,  'Cat-a' as series
select '2021-06' as x,  '20' as y,  'Cat-b' as series

select '2021-07' as x,  '25' as y,  'Total' as series
select '2021-07' as x,  '15' as y,  'Cat-a' as series
select '2021-07' as x,  '10' as y,  'Cat-b' as series

select '2021-08' as x,  '25' as y,  'Total' as series
select '2021-08' as x,  '15' as y,  'Cat-a' as series
select '2021-08' as x,  '10' as y,  'Cat-b' as series

select '2021-09' as x,  '30' as y,  'Total' as series
select '2021-09' as x,  '20' as y,  'Cat-a' as series
select '2021-09' as x,  '10' as y,  'Cat-b' as series

select '2021-10' as x,  '27' as y,  'Total' as series
select '2021-10' as x,  '15' as y,  'Cat-a' as series
select '2021-10' as x,  '12' as y,  'Cat-b' as series

select '2021-11' as x,  '60' as y,  'Total' as series
select '2021-11' as x,  '40' as y,  'Cat-a' as series
select '2021-11' as x,  '20' as y,  'Cat-b' as series

select '2021-12' as x,  '45' as y,  'Total' as series
select '2021-12' as x,  '25' as y,  'Cat-a' as series
select '2021-12' as x,  '20' as y,  'Cat-b' as series

select '2022-01' as x,  '30' as y,  'Total' as series
select '2022-01' as x,  '20' as y,  'Cat-a' as series
select '2022-01' as x,  '10' as y,  'Cat-b' as series

select '2022-02' as x,  '27' as y,  'Total' as series
select '2022-02' as x,  '15' as y,  'Cat-a' as series
select '2022-02' as x,  '12' as y,  'Cat-b' as series

select '2022-03' as x,  '45' as y,  'Total' as series
select '2022-03' as x,  '25' as y,  'Cat-a' as series
select '2022-03' as x,  '20' as y,  'Cat-b' as series

select '2022-04' as x,  '60' as y,  'Total' as series
select '2022-04' as x,  '40' as y,  'Cat-a' as series
select '2022-04' as x,  '20' as y,  'Cat-b' as series

select '2022-05' as x,  '30' as y,  'Total' as series
select '2022-05' as x,  '20' as y,  'Cat-a' as series
select '2022-05' as x,  '10' as y,  'Cat-b' as series

select '2022-06' as x,  '27' as y,  'Total' as series
select '2022-06' as x,  '15' as y,  'Cat-a' as series
select '2022-06' as x,  '12' as y,  'Cat-b' as series

select '2022-07' as x,  '45' as y,  'Total' as series
select '2022-07' as x,  '25' as y,  'Cat-a' as series
select '2022-07' as x,  '20' as y,  'Cat-b' as series

select '2022-08' as x,  '45' as y,  'Total' as series
select '2022-08' as x,  '25' as y,  'Cat-a' as series
select '2022-08' as x,  '20' as y,  'Cat-b' as series

select '2022-09' as x,  '25' as y,  'Total' as series
select '2022-09' as x,  '15' as y,  'Cat-a' as series
select '2022-09' as x,  '10' as y,  'Cat-b' as series

select '2022-10' as x,  '60' as y,  'Total' as series
select '2022-10' as x,  '40' as y,  'Cat-a' as series
select '2022-10' as x,  '20' as y,  'Cat-b' as series

select '2022-11' as x,  '27' as y,  'Total' as series
select '2022-11' as x,  '15' as y,  'Cat-a' as series
select '2022-11' as x,  '12' as y,  'Cat-b' as series

select '2022-12' as x,  '30' as y,  'Total' as series
select '2022-12' as x,  '20' as y,  'Cat-a' as series
select '2022-12' as x,  '10' as y,  'Cat-b' as series

select '2023-01' as x,  '27' as y,  'Total' as series
select '2023-01' as x,  '15' as y,  'Cat-a' as series
select '2023-01' as x,  '12' as y,  'Cat-b' as series

select '2023-02' as x,  '45' as y,  'Total' as series
select '2023-02' as x,  '25' as y,  'Cat-a' as series
select '2023-02' as x,  '20' as y,  'Cat-b' as series

select '2023-03' as x,  '25' as y,  'Total' as series
select '2023-03' as x,  '15' as y,  'Cat-a' as series
select '2023-03' as x,  '10' as y,  'Cat-b' as series

select '2023-04' as x,  '60' as y,  'Total' as series
select '2023-04' as x,  '40' as y,  'Cat-a' as series
select '2023-04' as x,  '20' as y,  'Cat-b' as series

select '2023-05' as x,  '45' as y,  'Total' as series
select '2023-05' as x,  '25' as y,  'Cat-a' as series
select '2023-05' as x,  '20' as y,  'Cat-b' as series

select '2023-06' as x,  '30' as y,  'Total' as series
select '2023-06' as x,  '20' as y,  'Cat-a' as series
select '2023-06' as x,  '10' as y,  'Cat-b' as series

select '2023-07' as x,  '27' as y,  'Total' as series
select '2023-07' as x,  '15' as y,  'Cat-a' as series
select '2023-07' as x,  '12' as y,  'Cat-b' as series

select '2023-08' as x,  '60' as y,  'Total' as series
select '2023-08' as x,  '40' as y,  'Cat-a' as series
select '2023-08' as x,  '20' as y,  'Cat-b' as series

select '2023-09' as x,  '45' as y,  'Total' as series
select '2023-09' as x,  '25' as y,  'Cat-a' as series
select '2023-09' as x,  '20' as y,  'Cat-b' as series

select '2023-10' as x,  '45' as y,  'Total' as series
select '2023-10' as x,  '25' as y,  'Cat-a' as series
select '2023-10' as x,  '20' as y,  'Cat-b' as series

select '2023-11' as x,  '27' as y,  'Total' as series
select '2023-11' as x,  '15' as y,  'Cat-a' as series
select '2023-11' as x,  '12' as y,  'Cat-b' as series

select '2023-12' as x,  '30' as y,  'Total' as series
select '2023-12' as x,  '20' as y,  'Cat-a' as series
select '2023-12' as x,  '10' as y,  'Cat-b' as series

select '2024-01' as x,  '60' as y,  'Total' as series
select '2024-01' as x,  '40' as y,  'Cat-a' as series
select '2024-01' as x,  '20' as y,  'Cat-b' as series
lovasoa commented 5 months ago

Hello and thank you for the report !

I'm am sorry for the unannounced breaking change to your charts :disappointed:

There hasn't been any explicit change to stacked bar charts inside SQLPage itself in v18 (outside of the default color scheme change), but the charting library that powers the charts (ApexCharts) has been updated from v3.44.0 to v3.45.1 (see changes).

The behavior you are describing sounds like a bug being fixed, in my opinion. By definition, different series in stacked charts do "pile on each other". It is unusual to manually include a "total" series in a stacked chart, since the total is identifiable visually (it's where the stack ends).

I'm not sure about adding a separate option for not stacking bars in stacked bar charts... It seems like an unusual thing to do, and when really needed, it can always be done in sql by subtracting values from the total.

In your particular case, what does your data source look like ? Does it have pre-computed sums instead of individual values for 'cat-a' and 'cat-b'.

accforgithubtest commented 5 months ago

Thanks for your reply @lovasoa ! and thanks for your reference to apexcharts.

Is there a way to pass additional configuration options to apex charts from sqlpage ? I would like to display the totals on top of the stacked bar chart, which could be useful. In this example, the bar charts are able to display the numbers within the bars that is useful as well. This will actually help to hide the y axis numbers and display them in the bars, which makes more sense for stacked bar charts, to get a sense of the values of stacked bars.

-- Re the older version of the bar charts -- I suspected that this was not something that can be fixed, as the current behaviour of "pile-on" is the usual behaviour for stacked bar charts, however I was pleasantly surprised when I found the "layered" version of stacked bar chart in here.

The data in my case is identical to the sample above, it is a time-series data with date, categories and daily totals. A sql is used to group by the month, along with monthly totals (not pre-compute, but generated with sum + group by) to get the final data which is identical above. The actual data has 12 categories/series.

The reason I found the older (buggy/unusual) version of bar char useful was i dont have to manually add a '0' entry in output data for missing date / month, since not all the category/series will have entry for every month. Adding the '0' to the output makes the sql a bit complex and also the output data size multiples since there are a large number of series and this makes the browser significantly slower. Ans displaying that data for 12 series was relatively less usable in line/area charts compared to bar charts, because of the missing zero's, the line connect the 2 available dots and doesnt drop to zero when there is no data for a particular category/series for a month. So, the layered bar chart was actually providing better data vis, as well as working with a smaller data size, was better overall compared to the other options. Would be great if a "layered" option is available as it does provide a meaningful solution to visualize the data in certain cases.