Ordering multiple columns with both ASC and DESC

Community Discussions
, , ,
1

I see how to sort by multiple columns (screenshot), but how can I sort the first factor, "Tasks[Status]" Ascending, and then Tasks[Due] Descending? I'm referring to the screenshot with the factors listed above.

My use case is to sort by date and then by title as there are often multiple items with the same date. Right now the sort is by Date DESC so the most recent is at the top, but the items are then also sorted by Name DESC , which makes "Z" at the top.

image
image704×185 6.23 KB

1 Like
3

Hi @Allen, thanks for your post!

When using the ORDER BY operator for multiple columns, you can specify how each column will be ordered by adding ASC or DESC after each column.

So the syntax to order the first by ascending and second by descending would be:
=Filter(Table, "ORDER BY Table[Column 1 to order by] ASC, Table[Column 2 to order by] DESC")

For your case, you would add your date column first, item name column second, and specify ASC or DESC accordingly for each.

Let me know if this helps. :honeybee:

5

Thank you, I had tried that previously and it did not work. I tried it again and didn't work. I then started thinking the issue is with the date field (Created Date is the field name). It appears the date field; although set to Date vs Date & Time, is still capturing and storing the time. As such, when I sort by it by date the sort includes the time.

Result is if there are 10 items on 8/14, I can't just sort by date then name as no two items have the same date when time is also being stored. Note, the date is being written via the Now() formula.

My work around was to created three new fields:

  1. Month Column: =Month([Created Date])
  2. Day Column: =Day([Created Date])
  3. Year Column: =Year([Created Date])

I then sort by each of these columns. This is probably more complicated than it needs to be, but it appears to be working.
image

7

hi @Allen, glad to hear you got it to work for you. Here are a couple more thoughts that may simplify life a bit:

First, you can try creating a single column that concatenates Year, Month and Day. This is similar to what you have done but more economical on new columns and easier on the search. You may try creating a column called "sortDate" with a formula like this:
=CONCATENATE(YEAR([Date]),"-",MONTH([Date]),"-",DAY([Date]))
but you have to be careful with months like 1, 10 and 11 as these will all sort before 2 (February). To work around that you can use a more complex formula like this, that includes padding:
=CONCATENATE(TEXT(YEAR([Date]),"0000"),"-",TEXT(MONTH([Date]),"00"),"-",TEXT(DAY([Date]),"00"))

Option 2, and maybe the easier option is to replace =NOW() with =TODAY(). Today defaults to the time as 0:0 so the time part of the date-time will not get in the way of your sort. But I understand, that this may work only when you never need the time part of the date; otherwise you can keep two columns one for =NOW() and one for =TODAY().

Hope this helps,
DT.

9

Agree on making it one column. I left separate so I have more flexibility in future regarding sort options and with separate columns I avoid the issue of 1, 10, 11, 12, 2, 3...

I do like the TODAY option as another option. Will likely make it a separate column so I retain the time in case I need it down the road.