Custom sorting from mongoDB query breaks after grouping fields

cny · February 22, 2022, 10:59am

I needed to create custom sorting based on some rules, so my data looks logical, but I ran into issues, when there is no data for one of the groups which the x value is on.

In order to create my custom sorting I added a column weight, and calculated the values, but the order breaks after the mentioned problem.

I tried adding dummy data where a script creates documents with value of 0 for my y axis and it kind of worked.

Here is my chart, it looks really ugly without the order and is unusable

I also tried searching for solutions for such issue, but to no avail, are there other things I could try?

Here is, the order I want and this is achieved by removing the x values from the list where not all groups have data or when I have dummy data in place

Is there a better way to do this maybe through redash or mongo, any advice helps.

ilyaminati · February 22, 2022, 7:29pm

Maybe these are relevant?

github.com/getredash/redash

"Treat missing/null values as 0" Does not work for line charts with datetime scale x-axis

opened 12:00PM - 09 Mar 20 UTC

orenpai

Frontend Visualizations: Chart Visualizations

### Issue Summary When creating a line chart with a datetime based x-axis, fr…om a table with missing dates ("holes"), the line chart continues as if no data was present, even though the option "Treat missing/null values as 0" is turned on. In contrast, bar charts work as expected - in which the bar is nonexistent (effectively size 0) on those missing dates. ### Steps to Reproduce You can use this csv data as an example, or use the attached CSV. [Incoming_Users_2020_03_09.csv.txt](https://github.com/getredash/redash/files/4306409/Incoming_Users_2020_03_09.csv.txt) dt,installs 06/02/2020,1 10/02/2020,8 11/02/2020,5 12/02/2020,1 (Please note European date format, switch month and day if necessary) Create a chart visualization (x-axis is dt, y axis is installs); Make sure the "Treat missing/null values as 0" checkbox is selected. In the x-axis settings make sure the scale is Datetime (default). Notice how the chart line continues straight in all dates between 06/02 and 10/02, as if they value continued to be "1" for these dates (even though indeed no marker is present when you hover the chart with the mouse cursor). Switch to bar chart - all bars in the same dates show a value of 0 (no bar). Notice how unchecking and checking the checkbox "Treat missing/null values as 0" does not alter either of these charts. Screenshots attached <img width="796" alt="BarChart" src="https://user-images.githubusercontent.com/20188692/76211109-28b16500-620e-11ea-872c-b59cb22f7cd1.png"> <img width="791" alt="LineChart" src="https://user-images.githubusercontent.com/20188692/76211112-2a7b2880-620e-11ea-97bb-327900a9c1bc.png"> The expected behavior is that when the checkbox is checked, the line chart would go to 0 on the dates that are not available in the table, similar to how the bar chart looks. For the bar chart, if the checkbox is unchecked, I would expect the same bar value to be kept, similar to how the line chart is. * Redash Version: 8.0.0+b32245 (a16f551e) * Browser/OS: Chrome Version 80.0.3987.122 (Official Build) (64-bit) * How did you install Redash: AWS ami-0b97435028ca44fcc

cny · February 23, 2022, 5:41am

I enabled stacking and excluded set missing values not to show up and got a lot better looking line chart

“The Treat missing/null values as 0” issue does not work for me by itself as well, so it could be relevant, but the order is still messed up, at least the chart looks bearable