Custom sorting is overriden when stacked bars don't contain the same categories

[jasonbowes]

I have an issue with my order by clause being overridden when the stacking is not consistent.

See override:

When stacking is consistent (no issues)

I am unable to use a datetime type for the x axis as I have a drop down which allows the user to change the granularity of the x axis (i.e by day, week, month, quarter and year) which requires me to have it as a consistent varchar type to achieve this.

Is there any way i can override the default behavior and ensure that the bars are sorted by the order in the SQL first and then stacking second order?

Cheers,

[k4s1m]

Just checking: have you already toggled off “Sort Values” in the visualization settings?

[jasonbowes]

Thanks for the response.

I have unticked sort values yes with sort values the bars are sorted alphabetically.

[k4s1m]

Gotcha. What do you mean “stacking is not consistent”? Can you share a sample data set that causes this behavior? Anonymized data is fine.

[jasonbowes]

Sorry hadn’t explained that very well. I mean when the X axis categories don’t have consistent data categories (as represented by the colors in the chart). See below for an example data hopefully this helps clarify what i mean. (this is an export directly from the table produced by the query in Redash which has then been anonymised).

time	mintime	category	value
Jan-20	31/01/2020 00:00	Blue	-
Feb-20	14/02/2020 17:33	Red	1.43
Mar-20	02/03/2020 12:25	Red	6.33
Apr-20	17/04/2020 10:46	Blue	6.04
May-20	01/05/2020 10:23	Blue	18.50
May-20	05/05/2020 07:07	Red	1.20
Jun-20	01/06/2020 09:43	Blue	8.43
Jun-20	04/06/2020 07:00	Red	2.19
Jul-20	01/07/2020 06:38	Blue	17.20
Aug-20	03/08/2020 06:56	Blue	16.00
Sep-20	01/09/2020 07:11	Blue	8.56
Sep-20	18/09/2020 08:58	Red	1.00

The “order by” is done on mintime, time is a varchar value which is changed based on a dropdown on the dashboard. This produces the following:

Thanks for the help!

[k4s1m]

Makes sense! Plotly draws records in the order it encounters them in your data set. Initially the data gets split by your grouping field so your one table becomes two:

Blue

time	mintime	category	value
20-Jan	31/01/2020 00:00	Blue	-
20-Apr	17/04/2020 10:46	Blue	6.04
20-May	1/5/20 10:23	Blue	18.5
20-Jun	1/6/20 9:43	Blue	8.43
20-Jul	1/7/20 6:38	Blue	17.2
20-Aug	3/8/20 6:56	Blue	16
20-Sep	1/9/20 7:11	Blue	8.56

Red

time	mintime	category	value
20-Feb	14/02/2020 17:33	Red	1.43
20-Mar	2/3/20 12:25	Red	6.33
20-May	5/5/20 7:07	Red	1.2
20-Jun	4/6/20 7:00	Red	2.19
20-Sep	18/09/2020 08:58	Red	1

Then it begins drawing bars by your X-axis field. It sees the first date is January 2020 and draws that blue bar (it’s invisible here because the value is 0). Then it checks the other group table(s) for records in January 2020. Seeing none, it goes to the next record in blue. And so on…

After this it starts going through the next group: Red. It sees a record in February 2020. This is the first time Plotly saw this date. So it adds it to the end of the chart and checks the other group(s) for records from this date. And so forth.

To fix it: select your dates and JOIN the blue and red data off of them. Then UNION the two sets. So that you have a natural, uninterrupted, repeating set of dates like so:

time	category	mintime	value
2020-Jan	Red
2020-Jan	Blue	31/01/2020 00:00	0
2020-Feb	Red	14/02/2020 17:33	1.43
2020-Feb	Blue
2020-Mar	Red	2/3/20 12:25	6.33
2020-Mar	Blue
2020-Apr	Red
2020-Apr	Blue	17/04/2020 10:46	6.04
2020-May	Red	5/5/20 7:07	1.2
2020-May	Blue	1/5/20 10:23	18.5
2020-Jun	Red	4/6/20 7:00	2.19
2020-Jun	Blue	1/6/20 9:43	8.43
2020-Jul	Red
2020-Jul	Blue	1/7/20 6:38	17.2
2020-Aug	Red
2020-Aug	Blue	3/8/20 6:56	16
2020-Sep	Red	18/09/2020 08:58	1
2020-Sep	Blue	1/9/20 7:11	8.56

[jasonbowes]

Ah that makes perfect sense, thanks very much for your help.

I hadn’t appreciated that it was splitting the table into groups first and then plotting the chart. I will do as you say and create a consistent and uninterrupted dataset.

Really appreciate your time and help.