Looking for an elegant way to group items based on the year of a record entry, then subsequently group by month.
Seems easy enough by using year() and month() expressions, but the issue i am having is with the display.
My grouping is working, but i would prefer the month name be showing in the sort instead of the number. IN my example, i have an activity tracker where a user will log time spent on a task. They enter a start time and an end time, both of which are Date Time data types.
When i look at a users logs, i get this when i drill down into a year:
I know that i can use Index() to convert a number to a desired month text format, but in doing so, i lose the correct sorting option. How do i sort by numerical month, but display the correct text?
When you pass a date into the text function you can format it with a string parameter like this
TEXT([DATE],“MM”) will return the 2 digit month. TEXT([DATE],“MMM”) will return the 3 character month name(Jan,Feb,Mar etc). TEXT([DATE],“MMMM”) will possibly return the full month name (January, February etc).
There are also values for days and years, so if you want to group your dates by Year-Month, use TEXT([DATE],“YYYY-MMM”).
The grouping is a little bit challenge as you are not able to sort the order with another column. You only have the option to select ascending or descending. Because of that, you would need to use for example TEXT([Date],“MM-MMMM”). With this way the result is 01-January, 02-February and so on. Then the sort works as you have two digits before the real month name. You need the zero because the string is a pure text, not a number. Otherwise the order will be wrong because of 10, 12 and 12.
Ahhh, but you are not supposed to sort using the grouped column
You sort your data with the [Date] column and group it by the VirtualColumn (TEXT([Date],“YYYY-MMM”). This guarantees that the grouped column will appear in the correct order and if the viewtype is a Table it will show a nice drillable tabulated display.
Not sure if this helps, but if sorting and grouping is so important to get perfect, I’d just create 4 columns called [Year], [Month], [Day] and [Time]. Virtual or spreadsheet, doesn’t matter.
Create a view. Group by [Year] then [Month], sort by [Day] then [Time].
Scott, I proposed this as he was asking.. “Looking for an elegant way to group items based on the year of a record entry, then subsequently group by month.”
