Build Array with Sorted Dates

Panorama X
1

I have a database with a Date field. There are many records with the same date. I want to populate a text list using a formula of the unique dates, formatted as “YYYY-Mon”. I cannot get this to sort properly. Here is what I am using now:

arraydeduplicate(arraybuild(cr(), “Volume”, {datepattern(Date, “YYYY-Mon”)}), cr())

It is sorting in alphabetical order rather than date order.

I would really like to get the array reverse sorted in date order (most recent date at the top).

Thank you in advance.

2

arrayfilter(arraydeduplicate(arraybuild(cr(),“Volume”,{month1st(Date)+¬+datepattern(Date,“YYYY-Mon”)}),cr()),cr(),{array(import(),2,¬)})

3

And the reason for the sort you were getting is because datepattern is turning the dates into text values.

4

because datepattern is turning the dates into text values.

Of course you have to turn the dates into text values, because arraydeduplicate( can only sort text values. That function doesn’t know anything about dates.

@epansoft 's solution works by first building an array where each element looks something like this (the ¬ character is really an invisible tab character):

2461073¬YYYY-Feb

The first part of this, before the tab, is the first day of the month in days since ~4000 BC. In this case 2461073 corresponds to Feb 1, 2026. You can try this yourself using the Formula Workshop.

image
image1176×690 57.8 KB

For modern dates, this value will always have 7 digits, so an alphabetic sort will actually sort the items in date order.

Once the array is sorted and deduplicated, the arrayfilter( function removes the 7 digit number that you don’t want, leaving your desired YYYY-Mon format, but sorted in date order.

5

Much faster and better version:

arrayfilter(arraydeduplicate(arraybuild(cr(),"Volume",{month1st(Date)}),cr()),cr(),{datepattern(import(),"YYYY-Mon")})

6

You can create a summary table and reverse the resulting array.

arrayreverse(summarytable("groups",{groupdatepattern(Date, "YYYY-Mon")},"format","plain nototals"),¶)

I ignore how this approach compares in speed to the previous suggestions.

@admin, Is there an option in summarytable() to omit the Titles line directly, similar to how the nototals option removes the Totals line? I often use summarytable() and end up removing the Titles line manually because I don’t need it.

7

You can create a summary table and reverse the resulting array.

arrayreverse(summarytable("groups",{groupdatepattern(Date, "YYYY-Mon")},"format","plain nototals"),¶)

I think this is definitely a superior approach to the arraybuild( approach discussed earlier. :+1:

I ignore how this approach compares in speed to the previous suggestions.

I didn’t test it, but I would guess if anything using summary table( would be the faster approach. Almost certainly not slower.

Is there an option in summarytable() to omit the Titles line directly?

@Hugo I think the toptemplate will do what you want - If you don’t want any top line at all (no column titles), set this template to empty text (“”).

image
image1556×810 88.8 KB

1 Like
8

For about 60k records:

arrayfilter(arraydeduplicate(arraybuild(cr(),“Volume”,{month1st(Date)+¬+datepattern(Date,“YYYY-Mon”)}),cr()),cr(),{array(import(),2,¬)}) takes about 95 ticks, but

arrayfilter(arraydeduplicate(arraybuild(cr(),“Volume”,{month1st(Date)}),cr()),cr(),{datepattern(import(),“YYYY-Mon”)}) takes only 4 ticks!

arrayreverse(summarytable(“groups”,{groupdatepattern(Date, “YYYY-Mon”)},“format”,“plain nototals”),¶) also takes about 95 ticks.

1 Like