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

Support
#1

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

See override:

image
image1769×553 22.2 KB

When stacking is consistent (no issues)

image
image1619×642 18.6 KB

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,

#2

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

#3

Thanks for the response.

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

#4

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

#5

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:

image
image1873×585 19.9 KB

Thanks for the help!

#6

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
1 Like
Wrong sorting order in visualization, but right in the table view
Data displayed in the wrong place in graph
#7

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.

1 Like
#8

HI

Facing the same issue, where character date is not srorted.

To have a workaround we selected 2 dates, one date format and one in character format.

Selected character date in X axis and provided original date in order by.

Because of group by this work around also not working.

We wont be able to use union all as suggested as our group by column is dynamic and it impacts performance.

Customers have raised this as a bug.

Implemented work arounds, date formatting, nothing seems to help