Sort filter values using order from another column

[ampil]

I have a virtual dataset that I use for filtering, which utilizes a PostgreSQL database to generate a range of dates:

SELECT startdate::date AS startdate,
      TO_CHAR(startdate, 'YYYY-MM-DD') AS startdate_str,
      TO_CHAR(startdate, 'DD.MM.YYYY') AS startdate_str_format
FROM generate_series('2015-01-01', NOW(), '1 day'::interval) AS startdate  
ORDER BY startdate DESC;

Currently, I use a Value filter that allows users to select date(s) from a dropdown menu. I would like the filter values to be well-aligned and sorted. However, when using string-format dates ('DD.MM.YYYY'), the output is not visually appealing: 01.01.2015, 01.01.2016, 01.01.2017, etc. (See the attached image.)

And I need 01.01.2015, 02.01.2015, 03.01.2015, etc.

Is it possible to display values from one column (specifically startdate_str_format) while sorting based on another column (namely startdate)?

I attempted to create a custom metric for sorting purposes with RANK() OVER(ORDER BY startdate DESC), but encountered an error:

column "virtual_table.startdate" must appear in the GROUP BY clause or be used in an aggregate function LINE 3: order by startdate DESC

Any help is appreciated.

[dosubot[bot]]

To achieve sorting based on one column while displaying another in Superset, you can use the "Sort Metric" feature. Here's how you can configure it:

1. Use the startdate for Sorting: Set startdate as the sort metric. This ensures that the dropdown values are sorted by the actual date.

2. Display startdate_str_format: Use startdate_str_format as the display column in your filter configuration.

Here's a general approach:

• In the filter configuration, specify startdate as the sort metric. This will sort the dropdown values based on the actual date.
• Ensure that startdate_str_format is used for display purposes.

This setup allows you to display the formatted date while sorting by the actual date, ensuring proper alignment and order in the dropdown.

Regarding the error with RANK(), it occurs because PostgreSQL requires all non-aggregated columns in the SELECT clause to be included in the GROUP BY clause. Since you're using a window function, ensure that the query structure aligns with PostgreSQL's requirements, or consider using the sort metric approach as described above.

To continue talking to Dosu, mention @dosu.

[ampil]

@dosu, that is not a valid answer. Configurations 1 and 2 are not available on Superset's native filters. Did you reference to the information regarding filter boxes? According to #19184 and #26084 the issue in my request is much requested feature that is not yet available.

Please extend your reply regarding the error.
How can I create a metric on a virtual dataset with a group by clause?

I tried ROW_NUMBER () OVER (ORDER BY startdate) - when using on a plain dataset it returns row numbers without errors.
When using this expression inside a metrics, I still face an error.

Take into account that I use Superset 3.0.0 with native filters.
Suggest any workarounds regarding the initial issue.

[dosubot[bot]]

To create a metric with a GROUP BY clause in Superset, you need to ensure that all non-aggregated columns in your SELECT statement are included in the GROUP BY clause. Here's a general approach:

1. Modify Your Query: Ensure that your query includes all necessary columns in the GROUP BY clause. For example:

   SELECT 
       startdate,
       TO_CHAR(startdate, 'DD.MM.YYYY') AS startdate_str_format,
       ROW_NUMBER() OVER (ORDER BY startdate) AS row_num
   FROM 
       generate_series('2015-01-01', NOW(), '1 day'::interval) AS startdate
   GROUP BY 
       startdate, startdate_str_format

2. Use Window Functions Carefully: When using window functions like ROW_NUMBER(), ensure they are used in a context that doesn't conflict with the GROUP BY requirements.

3. Workaround for Sorting: Since native filters don't support sorting by a different column directly, consider creating a view in your database that pre-sorts the data and use that view in Superset.

Regarding the error with ROW_NUMBER(), ensure that the query structure aligns with PostgreSQL's requirements. If you're still facing issues, you might need to handle the sorting logic outside of Superset, such as in a database view or a materialized view, and then use that in your virtual dataset.

To continue talking to Dosu, mention @dosu.

[kpiforgeoliver]

The Work-around for sorting does not work at all. No matter how you sort the query, the pivot table chart tries to sort things by the First column name alphabetically or numerically. We want to be able to display the rows in an order that we define!

[ampil]

A workaround for sorting dates in a custom format ("DD.MM.YYYY"):

1. Create a virtual dataset with dates - for testing purposes keep original and text-formatted columns:

select enddate::date as "ENDDATE"
      , to_char(enddate, 'DD.MM.YYYY') as "ENDDATE_STR_RU"
from generate_series('2024-01-01', now(), '1 day'::interval) as enddate  
order by enddate desc;

That is for PostgreSQL , adjust for your database.

2. Create a new measure in this dataset (three dots - Edit dataset):
   nullif(RIGHT("ENDDATE_STR_RU", 4) || substring("ENDDATE_STR_RU", 4,2 ) || left("ENDDATE_STR_RU", 2), ''):: int
   Name it as ruSort, for example.

3. In the filter settings, add new filter based on the dataset. Select a column with the custom date format ENDDATE_STR_RU. Then add sorting by a recently created measure ruSort.

Now you should see the dates in your own sort instead of default A-Z:

[MammadTavakoli]

I have a solution is stackoverflow