Sorting Alphabetic values to come after Numeric values

[Jojo_Stella]

Hey, I’m trying to list a series of calendar events grouped by month. They are sorted by the numeric value of the month (data set format looks like: 1//1/2021) -So far this works correctly, however I’d like the event’s marked as “NO DATE”, to be sorted at the top in a list of newest to oldest (so it would need to in this case be seen as the 13th “month”). Currently the expression is doing the exact opposite of what I want and NO DATE items end up at the bottom with oldest dates, and I can’t seem to locate documentation on the expression that would help me here… I was curious if anyone had a solution?

IF(ISBLANK([Event Date]),“NO DATE”,CONCATENATE(
SWITCH(
MONTH([Event Date]),
1,“January”,
2,“February”,
3,“March”,
4,“April”,
5,“May”,
6,“June”,
7,“July”,
8,“August”,
9,“September”,
10,“October”,
11,“November”,
12,“December”,
“”
),
" ",YEAR([Event Date])
))

[Suvrutt_Gurjar]

I believe AppSheet groups by digit value and alphabetically. So , if you have dates with September and October, I believe “No Date” group will appear in between before those months because N is alphabetically before O and S.

So if you wish just “No Dates” to apepar at the top, you could attach a digit before “No Date” such as “0-NO DATE”

Also with your current naming convention, April and August dates will appear before February and March and so on.

You may wish to attach month digits before month names to sort the grouped dates by month in alphabetical order.

Also you may wish to use the TEXT() function to simplify the expression as below

IF(ISBLANK([Event Date]),“0-NO DATE”,TEXT([Event Date], “MMMM YYYY”))

The following will add a digit corresponding to the month before month year names

IF(ISBLANK([Event Date]),“0-NO DATE”,TEXT([Event Date], “M- MMMM YYYY”))

help.appsheet.com

TEXT()

Format as text

[Jojo_Stella]

The dates in the actual data columns are numeric. This just converts the numeric version into each proper spelled out month, then sorts the months appropriately. Just NO DATE goes at the bottom after the oldest dates.

[Suvrutt_Gurjar]

Jojo_Stella:

The dates in the actual data columns are numeric

Could you elaborate what you mean by numeric dates?

[Jojo_Stella]

In the actual data columns the dates are formatted like 1/1/2021 or mm/dd/yyyy

This expression assigns a month to each number, and parses out the year. I have the table grouped by month so it displays groups of calendar events within January 2021, Feb 2021, March 2021, etc

So it’s still sorting by number, but putting NO DATE after any number sorting. The only way to get it to sort to the top with a number would be to somehow assign ‘13’ next to NO DATE in it’s current configuration as opposed to ‘0’ as you suggested.

[Suvrutt_Gurjar]

Jojo_Stella:

In the actual data columns the dates are formatted like 1/1/2021 or mm/dd/yyyy

Okay, thank you, In that case, all that I mentioned in my earlier post is valid.

“NO DATE” will sort before September and October months.

Also I request you to try the shorter version of the expression that is exact alternative to SWITCH() based expression.

[Jojo_Stella]

No the table is sorting numerically. Top to Bottom: 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, NO DATE; with each number assigned to a month, and starting again each year change, with null dates at the end. It is NOT sorting Alphabetically.

I need it to sort like NO DATE 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1

[Jojo_Stella]

Here is a screen shot of it properly sorting the months by number, not alphabetically.

[Suvrutt_Gurjar]

Oh okay, thank you. In that case you may attach 0 to the “NO DATE” such as “0-NO DATE” or “13-No DATE” and still try the expression

IF(ISBLANK([Event Date]),“0-NO DATE”,TEXT([Event Date], “MMMM YYYY”)) which I believe is exact equivalent of the below except for “NO DATE” preceded by a 0 or 13

IF(ISBLANK([Event Date]),“NO DATE”,CONCATENATE(
SWITCH(
MONTH([Event Date]),
1,“January”,
2,“February”,
3,“March”,
4,“April”,
5,“May”,
6,“June”,
7,“July”,
8,“August”,
9,“September”,
10,“October”,
11,“November”,
12,“December”,
“”
),
" ",YEAR([Event Date])
))

Edit: Minor changes in description

[Jojo_Stella]

Here’s a picture of what I mean. But NO DATE labelled events are currently at the bottom of this list

[Steve · Accepted solution]

Add a virtual column to the table with an App formula expression of ISNOTBLANK([Date]). Add the column to your sort columns but do not display it.

[Jojo_Stella]

That’s brilliant man. Thank you so much!