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