Group Data by Month-Year and not Full Date

https://www.appsheet.com/Template/AppDef?appName=NewTestPhotoUpload-3272358

I want to group my data by month and not the full date. Please let me know a way as to how i can do that? Creating a virtual column doesnt work as the formula returns a Text For eg 1 for January, 2 for February etc…I feel this should have been a standard and easy feature but havent found a way yet!

Using the Month or eomonth formula returns month as a number and appsheet doesnt detect numbers as a date…

KIndly Help

Hi @AnujKhadria, and welcome to the community !

You may want to use a virtual column, with a combination of
SWITCH and MONTH([TimeStamp])

Something like:

SWITCH( MONTH([TimeStamp]),
   1,"january", 
   2, "february",
   ....
   11,"november",
   "december"
)

EDIT : forgot a comma

2 Likes

Dear,

This is the formula i Typed and this is the error I am getting. Please Help.

Column Name is “Date” in my table.

Formula As Follows:-

=SWITCH(Month([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”)

Error as follows:-

SWITCH function is used incorrectly: Inputs to SWITCH() must be an initial expression, one or more value-result pairs, and a default result

1 Like

@AnujKhadria

The last input is a default value.
No need to explicit 12 for december :slight_smile:

SWITCH (valueToTest,
   value1, result1,
   value2, result2, 
   defaultResult
)

Please have a look to the link I provided above :wink:

Hi,

This formula is working but i am unable to group by Month-Year.

1 Like

Good for the formula :+1:

For grouping, please navigate in the UX panel, and pick a view type that matches your expectations (Table, for example).
image

Then, choose the “Group by” option you want.
image
In your case, the month_year column.

If that answers your question, please remember to tick the post as “Solution” :wink:
Let us know if that works for you !

2 Likes

I already tried this but this still groups by Full Date!

Grouping by month should have been easier i feel, Shouldnt be so complicated, its so basic!

Can you please provide a capture of the view panel you are using, its result on the preview and the section of the Table where you set name, formula, show/editable/require etc for this column Month_year ?

1 Like

Just completed it and it group by Month for anyone who’s going to come across it.
Now, just have to add year too.

Incase it helps anyone out:

CONCATENATE(
YEAR([SALES_DATE]),
"-",
SWITCH(Month([SALES_DATE]),1,“January”,2,“February”,3,“March”,4,“April”,5,“May”,6,“June”,7,“July”,8,“August”,9,“September”,10,“October”,11,“November”,“December”)
)

It will give YEAR-MONTH_NAME: 2021-August

2 Likes

Hi @LengliimAdmin

Very nice to share :+1:

Another way to proceed, that I tested earlier today:

CONCATENATE(
YEAR([SALES_DATE]),
"-",
SWITCH(Month([SALES_DATE]),1,“January”,2,“February”,3,“March”,4,“April”,5,“May”,6,“June”,7,“July”,8,“August”,9,“September”,10,“October”,11,“November”,“December”)
)

can also be achieved this way:
TEXT([SALES_DATE],"yyyy-mmmm")

However, I have no idea of the most efficient expression :partying_face:

For reference:

2 Likes

Thank you for sharing. It looks much cleaner now.

3 Likes

Assuming English is the desired language.

Hi @Steve
I thought so, but…actually, that works with French too :partying_face:

I just set the locale table to French, so I guess now that’s OK.

Then I put this expression:
TEXT([TimeStamp],"YYYY_MM_DD (MMMM - DDDD)")
here’s the result
image

People at the Dev Team have been working very nice !

2 Likes

Whoa! Neat! Thanks for educating me!!!

:smiley:

2 Likes

It’s possible to teach something to @Steve …wow…
I feel like going from padawan to something else :nerd_face:

3 Likes