Table View with Group Aggregate "Date" with format MMM-YYYY?

Hello everyone,

I think I know the answer already (“not supported”) but yet I want to ask.

This is somehow related to a previous question I had posted which was about how to format a Date column (see: Is it possible to display Date column values in a different format?)

At this point in my app, I have a table view that lists “EXPENSES”, and each row has a [Date] column.

I’ve created also a [Month] virtual column of type “Text” with this formula: TEXT([Date],“MMM-YYYY”) as it was the suggestion from the previous question (See link above). Also, this new virtual column is the “Label” for my table. The values in that column look like: “Jan-2021”, “Feb-2021”, “Mar-2021” and so on.

Then, I wanted to Group Aggregate the EXPENSES by this virtual column, but the issue that I have is that the group gets sorted “alphabetically” and not “chronologically”… (clearly, because the virtual column in the end is a “Text” and not a “Date”).

I’ve tried changing the type of the [Month] virtual column to be of “Date” type, and while in this case the sorting is correctly (chronological), the Group Aggregate instead of showing something like “Jan-2021” displays “1/1/2021”

So, my final objective would be to be able to have a Table with a Group Aggregate of type “Date” where the “Date” values in the Group Aggregate would be displayed in the “MMM-YYYY” format, while still keeping the “chronological” sorting.

Is it there any way to achieve this?

As per my understanding, it will not be possible. If you use date format, it will show like “1/1/2021” and if you group by month names- Year , it will sort alphabetically.

If you are willing to take a look at a workaround, not so visually elegant, then there may be a way.

1 Like

Yes, that is what I though… so, I ended up doing a “hack”, like this:

CONCATENATE(
TEXT(
[_THISROW].[Date],“yyyymmdd”), // This is like a “Timestamp”
" (",
TEXT([_THISROW].[Month],“MMM-YYYY”),
“)”
)

The end result looks like this:

20210401 (Apr-2021)

It is not the prettiest, but it does the job.

2 Likes

Very good. Thank you for the update.