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?
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:
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:
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.
