RESOLVED I'm trying to create a way to group...

AppSheet AppSheet Q&A
1

RESOLVED

I’m trying to create a way to group entries by year and month in descending order.

I created a virtual column to extrapolate the year and month from the date and time stamp for each entry.

=CONCATENATE(YEAR([Created]), MONTH([Created]))

First the result shows up as 20175, 20176 etc, which is visually unappealing. I’d like to be able to convert this to something like 2017-5 or 5-2107 or better yet May 2017. Is there a way to do that?

Secondly when I group these entries by the Year Month column in descending order, I get an unexpected result.

I’ve tried displaying two different ways 1. month first/year last 2. year first/month last. Neither way gives me a chronologically accurate list.

Any ideas?

2

One simple solution is to just have 2 layers of grouping; first by descending order of YEAR([Created]), and second by decsending order of MONTH([Created]).

But if it really matters to have year-month names all on one list, then you could change the above formula with something like this:

=CONCATENATE(YEAR([Created]), IF(MONTH([Created])<10,“0”,“”), MONTH([Created]))

3

How about something like… CONCATENATE(YEAR([Created]),“-”,IFS(MONTH([Created])<10, “0”),MONTH([Created]))

4

Thanks Aleksi! My formula had a redundant CONCATENATE function; updated it!

5

Thanks @RezaRaoofi and @Aleksi_Alkio I’ll give this a go.