Group Data by Month-Year and not Full Date

AnujKhadria · 05-05-2021 12:52 AM

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

Aurelien

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

AnujKhadria

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

Aurelien

@AnujKhadria

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

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

Please have a look to the link I provided above

AnujKhadria

Hi,

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

Aurelien

Good for the formula

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

Then, choose the “Group by” option you want.

In your case, the month_year column.

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

AnujKhadria

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!

Aurelien

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 ?

111182

Hello, i just read all things you typed and its very nice but the problem is i cant group with Month still any ideas how to group with month ?

Steve

Meaning what?

LengliimAdmin

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

Aurelien

Hi @LengliimAdmin

Very nice to share

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

For reference:

LengliimAdmin

Thank you for sharing. It looks much cleaner now.

Steve

Assuming English is the desired language.

Aurelien

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

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

People at the Dev Team have been working very nice !

Steve

Whoa! Neat! Thanks for educating me!!!

Aurelien

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

shahappsheet

Thanks to you all the nice guys.🙏😁

shahappsheet

Hi @Aurelien ! Thanks for the response. In this context I would like to share one instance that when the date format was changed from the default to "dd/mm/yyyy", it orders the data differently for the group by data, so far I had gone through.

Thanks.