Sort Month Name

[App_Creator]

Hi!

I would like to find out if there’s any way to sort month name according to month order (Jan, Feb, Mar, Apr, …) instead of alphabetical order (Apr, Feb, Jan, Mar, …)?

Thank you!

[LeventK]

@App_Creator
May I ask where and how you are using that month names? And how you get them? With long date setting? Please elaborate.

[App_Creator]

Thanks LeventK.

I got the month names using switch expression and the names became Text. Hence sorting according to alphabetical order.

SWITCH(MONTH([Date]), 1, “Jan”, 2, “Feb”, 3, “Mar”, 4, “Apr”, 5, “May”, 6, “Jun”, 7, “Jul”, 8, “Aug”, 9, “Sep”, 10, “Oct”, 11, “Nov”, “Dec”)

I am using these month names to group a set of entries and sort in desending order according to the month in a table view.

Thank you!

[LeventK · Accepted solution]

@App_Creator
If it’s not a big deal for you, slightly change your SWITCH expression:

SWITCH(
MONTH([Date]),
1, “1-Jan”,
2, “2-Feb”,
3, “3-Mar”,
4, “4-Apr”,
5, “5-May”,
6, “6-Jun”,
7, “7-Jul”,
8, “8-Aug”,
9, “9-Sep”,
10, “10-Oct”,
11, “11-Nov”,
“12-Dec”
)

[App_Creator]

That’s a great workaround! I will use that for the time being.

Thanks for your help, Leventk!

[LeventK]

You’re welcome @App_Creator, my pleasure. Great to be helped of.

[Michael2]

@LeventK

Any idea why the results are formatted as timedate with this solution? The date column this expression refers to is formatted as a date column yet the results of the SWITCH expression are formatted as timedate?

[LeventK]

@Michael
That’s basically because AppSheet interprets “1-JAN” string as a short format Joda Date and appends 12:00:00AM to it and therefore evaluates the string as “1st of January 12:00:00AM”, “2nd of February 12:00:00AM”, “3rd of March 12:00:00AM” and so on. As @Aleksi mentioned check the type of your Virtual Column and change it to Text if not.

[Aleksi]

What happens if you change your column type to text?

[Michael2]

@Aleksi Although I have changed the date column to be formatted as text: TEXT(DATE([TimeStamp])), the result is still formatted as a date and therefore the SWITCH expression still results as timedate. The switch expression column is also formatted as text.
@LeventK

[Mike_T]

I’m still struggling with the idea that there has to be a way to display just the name of the month (without the preceding number and hyphen) and still have it sorted by month. Can anyone think of a way to “alias” a VC? Some sort of sort by “this” but display “that”? This is an old thread, has this been solved somewhere else?

[Steve]

Mike_T:

Some sort of sort by “this” but display “that”?

“This” would be a Ref value and “that” would be its label.

[Mike_T]

Yeah. I was thinking that Ref might be the way to go but then if i’m using the label in any other view that will throw that off. I’m still wrapping my head around all of the possibilities with Ref/Labels. I’ve probably overbuilt this particular table and am trying to do too much with it. Should build in more smaller Ref tables. I’ll keep at it and see what I can figure out. Thanks for the reply!

[Bahbus]

This is a perfect example of when a read-only table is appropriate.
[ID] is your key column, with 1-12.
[Month] is your text label column.

I do this exactly but for day of the week, and for the exact same reason.

[Daniel_Tanguay]

The problem is Appsheet. There is a bug here. it simply does not recognize that you have set the column set to “text” when a Joda Date is present regardless of all we are doing.

I have been trying to solve this for years.

[Steve]

Daniel_Tanguay:

There is a bug here. it simply does not recognize that you have set the column set to “text” when a Joda Date is present regardless of all we are doing.

Huh?

[Sagar_Udasi]

Create a virtual column with Enum type and enum entries as month names in order ‘Jan’, ‘Feb’, ‘Mar’ then use formula and switch statement to convert your date to month exactly like your enum. Then sort ascending and descending. It should work as expected.

[Brandonch2]

Hi!

I was trying to solve that.

Your answer was simple, short and the Best Option

😉

[FaCe]

Hi,

do you think i could solve my similar issue i'm having with this scenario ?

If yes, how ?

I've a table view that is grouped by Date 27/3/2023

I'd like to show Date in my native language (italian) as Lunedì 27 marzo 2023

as i've done elsewhere (i.e. in detail views)

using 2 virtual tables VTGiornoSettimana and VTMese

CONCATENATE ([VTGiornoSettimana], " ", TEXT([DATA], "d"), " ", [VTMese], " ", TEXT([DATA], "yyyy"))

How to achieve the same result in Table view leaving the grouping by date but changing date with my italian string concatenation ?