Sort chart x axis.
I've created a bar chart with a text column as the x axis, and a simple percentage as the y axis. The SQL returns the text column ordered by the percentage, but the chart renders the x axis in alphabetical order. See the attached redacted chart.
Can I configure the chart x axis to respect the ordering returned by SQL?
Hey Kirby and Nicolas , I wanted to let you know that sorting charts in Mode is now available via our new Quick Sort feature! https://twitter.com/ModeAnalytics/status/938567237849423872
Please dive in and let us know if you have any questions.Reply
Hey Mike DeCarlo thanks for letting us know about this new feature!
I've just tried it, and it's great !
A few observation (after 30 mins usage)
1. To have the sort order work with reports filters, you *have to* add the field you want to filter by to the filter section of the chart interface. That's not completely obvious, as report filters work without having to add fields there.
2. Quick sort does not work with grouped bar charts. It would be great if it did. Having the sort order be determined by the first measure added to the chart would be very natural to me.Reply
Thanks for your feedback, Nicolas . For the first issue, I'm having a hard time following what it is that you're seeing, versus the expected behavior. Can you create an example to share?
As for Grouped Bar Charts, Quick Sort orders series within groups, looking across the groups for the aggregation. It does not order groups themselves. In this example, the sum of the count of incidents for LARCENY/THEFT across all four pd_districts in the chart is greater than any other series, so that series gets first position when sorting by Descending: https://modeanalytics.com/modeanalytics/reports/3a825df63e2d .
We believe this behavior is preferable under the assumption that a Grouped Bar Chart is more valuable for comparisons between series, rather than comparisons of totals of all series in given categories (which might better be reflected in a Stacked Bar Chart). Moving forward, we'd like to support more robust and user-defined sorting operations, so please stay tuned!
In the meantime, can you share with us the type of data you're using in your Grouped Bar Chart? What are the categories on your X-Axis that would benefit from being ordered, and why would that be preferable to a Stacked Bar Chart?Reply
From what I can tell, OPs problem remains unsolved. Is there any way to preserve original SQL result set order in x-axis? New buttons only allow to sort x-axis by their y-values. If x-axis values are of plain text type, alphabetical order is forced for some reason.
https://monosnap.com/file/gdjqyYFDNJ5E8IthvjoO6lOlZSqyiF.png (see how x-axis values are April -> August)
I understand I can just add a date in format of YYYY-MM-DD and it will be sorted, but I have to reflect the period and this is the preferable format of date. SQL allows for a lot more complex sorting algorithms that any GUI one can possibly build does.Reply
Hey Igor , thanks for sharing your example. I agree with your point about how code can yield more elegant sorting patterns, and the value in having a visualization tool respect that.
We're currently thinking through how our UI can support different sort types, including something that would appear to satisfy the use case from your example--namely, sorting by a field not being used in your chart's configuration. (You're ORDERing BY start_date, yes?)Reply
Thanks for reaching out. No, I'm trying to sort by period_text which is a field being used for x-axis. So the use case is pretty basic to my mind as I want to just present the data as it is in SQL result, no reordering needed. It's even less sophisticated than what you already have imho :)Reply
Big +1 to Igor's point, it seems crazy that the default sorting isn't what's already in the query. My scenario is this:
(a) I want my x axis to be labels (so I can read the chart)
(b) I don't want to sort by my y values
(c) my query returns the correct order.
Legacy charts does this by default, and it makes the most sense as a default, but new charts orders by x-axis (so in this case, alphabetically).
A partial solution would be to allow a field to be shown on hover, that way my x-axis labels could be numbers (not ideal, but works), and I could still interpret my chart.
But better still - the most intuitive way - the way you use to do it!Reply
Josh Devlin hey, thanks! I believe what Mode could probably do is:
1. introduce a new enum database field for their charts table. it should probably be named `sort` and by default should be null.
2. run a migration to update all their existing charts to be `sort=alphabetical` so that none of their clients' charts break and proceed to be alphabetically sorted.
3. introduce UI element that allows to chose the sort method having at least `none` and `alphabetical`.
4. after that they can safely turn off enforced alphabetical sorting for any new charts leaving a choice to the user -- whether to preserve original SQL result set order or to enforce any other order.Reply
Is there still no update on this? In my very common example, I have bar groupings that are tiers ('Below 100', '100 to 499', '500 to 1000' and 'Above 1000') and I want to sort them in the order above, rather than alphabetically. I don't want to use a stacked bar b/c I am showing several different measures for each group, which are not appropriate to stack.Reply
Josh Devlin Preston Maness Jessica Thompson Ben At this time we do not have any updates regarding additional sorting options for series applied to the x-axis. We certainly appreciate this valuable feedback, and as Mike DeCarlo mentioned previously this is something that has been on our radar for quite a while.
In the meantime, you can potentially use one of the following workarounds to display charts in the desired order:
- Append a sortable character to your bucket names. For example: 1, 2, 3, ... or a, b, c, ...
- Use a Python library such as Plotly to render the chart, such as in this example report.
I know these solutions aren't ideal, but I hope they help. I will also definitely keep everyone here in the loop regarding when this functionality is available to be used in Mode!Reply
Hi Nate Festinger and Mike DeCarlo - so I realized today that this issue also affects date based x-axes. It is sorting the x-axis alphabetically instead of based on the date. When will this bug be fixed?
The charts are unusable for time based queries which are very straightforward use cases for displaying charts over time. Is there another work around for time based queries? This seems like a major issue with the current charting engine.Reply
Hey Tanner Hearne , thanks for weighing in. In Mode Charts, chronological sorting should occur by default when the x-axis data is of type date.
Can you confirm if your x-axis data is of type date, or something else (maybe string?)
If the type is string, you could transform your date format to YYYY-MM-DD, which would display in the chronological order by virtue of their alphabetic ordering.
Note that as an alternative, you can use a Python library such as plotly as a workaround for ordering the x-axis by result order, as in this example report.Reply
Nate Festinger Ah! That makes sense. I guess my next question is: Is it possible to add another date format that includes the day of the week?
We like to be able to see if it is a Sunday, Monday, etc.
The current date format we use is "2018-01-01 — Monday" so that it works alphabetically like you mentioned. I'd like it to be "MM-DD Monday" so that we can exclude the year for brevity's sake. However that isn't an option in the date format picker.
Thanks for your response!Reply
Hey Nate Festinger - I can generate it in my query, but then I can't cast it as the date type in Mode charts to get chronological ordering. I have to format my date in my query (including the year) and then cast it as a string to Mode charts.
So I think what would help is if that was an option in the Mode Charts date type dropdown.Reply