Aggregate different keys into one column and sort by this column

[dobsi]

What I’m trying to do

I have files with different keys for date-like values, such as date_A, date_B, date_C.
I would like to get a TABLE that has a column date which only shows the latest of the three dates for each row. Also, I would like to sort the TABLE by this new date column.

Things I have tried

I already tried the solution mentioned in https://forum.obsidian.md/t/dataview-aggregate-different-keys-to-show-as-one-column/72257 but it seems that this does not work for more than two keys and the sorting does not work as expected.
I also tried something like max(split((date_A + "," + date_B + ", " + date_C), ", ")) as date but sorting according to date does not work for this.

Both dataview or dataviewjs would be fine for me. Any help on this would be very appreciated!

[holroy]

What do you mean with “date-like” values? Would max(list(date_A, date_B, date_C)) work for you?

Could show some examples of your values?

[dobsi]

Thanks a lot for your reply!
With “date-like” I just mean that the values are dates in the YYYY-MM-DD format.
Your solution works well for displaying the maximum dates, however, what is still not working for me is to sort the table then by this new max_date. (Also, in a next step I would also like to filter by max_date and only show entries where this max_date is within a defined range, like only max_dates that are in a specific year.)
Here is what I tried:

TABLE
date_A,
date_B,
date_C,
max(list(date_A, date_B, date_C)) as max_date

FROM "Testing"
SORT max_date ASC

Which results in the table below. As can be seen, this is not sorted by max_date.

[Pch]

You could try sorting by max(list(date_A, date_B, date_C)) too .

TABLE
  date_A,
  date_B,
  date_C,
  max(list(date_A, date_B, date_C)) as max_date
FROM "Testing"
SORT max(list(date_A, date_B, date_C)) ASC

[holroy]

If you don’t want to just show a calculated value in a column, but want to use it for filtering, sorting and grouping, then use FLATTEN.

TABLE
date_A,
date_B,
date_C,
max_date
FROM "Testing"
FLATTEN max(list(date_A, date_B, date_C)) as max_date
WHERE max_date.year  = 2023
SORT max_date ASC

Update: Corrected the query according to OP’s reply below.

[dobsi]

Thanks a lot! Now I got the expected result.
The WHERE dateformat(max_date, "yyyy") = 2023 does not work, but WHERE max_date.year = 2023 does.