Wrong X-Axis Order when Visualization is Put on Dashboard

[Wout]

I have a visualization in which the X-axis values are displayed correctly in the Query Editor, in the order produced by the SQL query. However, when I add the visualization to a dashboard, the values are suddenly not sorted anymore.

How is this possible? Is this is bug in the Databricks UI? How do I fix the problem?

Correct (visualization in query editor): 

Wrong (visualization on dashboard):

 

I think this problem might be similar to https://community.databricks.com/s/question/0D58Y00009AICBTSA5/order-by-disabled-in-sql-dashboard; unfortunately, that question does not have an answer.

[Debayan]

Hi @Wout Maaskant​ , Could you please confirm what is the expectation regarding dashboard visualisation?

If I compare one instance, lets say "1 hour(s)" then I see both the graphs are saying the same and the position is same (it is spiked), where as in 6 and 7 hours its the second highest spike.

By default, dashboards are sorted in reverse chronological order. You can reorder the list by clicking the column headings. But in the SQL dashboard I do not see it is in order. Is it what you are asking for?

(Ref: https://docs.databricks.com/sql/user/dashboards/index.html#view-and-organize-dashboards)

[Wout]

Hi @Debayan Mukherjee​,

Thanks for your reply.

You are correct in that the values displayed in the graph are the same. (E.g. the spike at "1 hour(s)" has the same value on the y-axis.) I expect the x-axis values to be sorted in the order the SQL query returns them. When I view the visualization in the Query Editor, the x-axis values are sorted correctly, but when I view the same visualization on the dashboard, the x-axis values are sorted incorrectly.

> By default, dashboards are sorted in reverse chronological order. You can reorder the list by clicking the column headings.

I do not believe this quote is not relevant, as it talks about the order of dashboards in the dashboard list.

[Wout]

@Debayan Mukherjee​ @Lindsay Olson​ I believe this is a bug in Databricks SQL. Could you please pass this on to second-level/next-level support?

[Anonymous]

Absolutely, @Wout Maaskant​ ! Thanks for commenting - the more voices, the better!

[Wout]

It was by going through Databricks Support that we got an answer to this question. I am reposting it here in case others run into the same issue:

---

I researched and found that this is expected behaviour with sorting when added to the dashboard.

Unfortunately, "ORDER BY" is not supported in backend aggregation.

This issue is being caused by new aggregation visualization. In the dashboard, backend aggregation is always used but backend aggregation doesn’t inherit the order by clause specified by the user. The workaround is to use X-axis sort in the visualization editor so the X-axis values are always sorted in the front end.

Although, We have a product feature ticket created to support it. there is no ETA at this moment.

[Wout · Accepted solution]

We have further analyzed the visualization problem and found two solutions.

The original visualization consists of 1 series and has aggregation enabled in the UI (but is unused, since the query itself aggregates already).

We found that the following two changes/workarounds solved the problem:

• Adding a 2nd series that visualizes a constant that uses a window function that repeats the desired ordering: "max(0) OVER (ORDER BY binSortUnit, binSortValue ASC) as constantWindowFunction"
• Disabling aggregation in the UI: in the visualization edit window, on the "General" tab, click on the three vertical dots next to "Y columns" and click on "Disable aggregations"

The second solution is the easier one, obviously.