Blog

Data - How to implement circular rotational date sorting

There can sometimes be a requirement to implement circular or rotational sorting, particularly in the case of data that contains dates. This post describes the formula to carry out this task.

An interesting requirement that can arise is the requirement to implement circular sorting. This occurs typically when we want to sort date ranges based on today's date.

For example, given an input list of data, if today is Wednesday, a circular sort implementation will sort records starting with Wednesday, followed by Thursday, Friday, Saturday, Sunday, Monday, and finally Tuesday.

For reference, there are a couple of forum posts that provide more details.

How to apply circular sorting based on weekday name

To give an example, let's take a list of meeting hosts. 

ClearCollect(
    colMeetingHosts,
    {DayName:"Monday", MeetingHost:"John Smith"},
    {DayName:"Tuesday", MeetingHost:"Mary Jones"},
    {DayName:"Wednesday", MeetingHost:"Robert Lee"},
    {DayName:"Thursday", MeetingHost:"Lisa Chen"},
    {DayName:"Friday", MeetingHost:"James Wilson"},
    {DayName:"Saturday", MeetingHost:"Sarah Green"},
    {DayName:"Sunday", MeetingHost:"David Miller"}
)
The requirement is to show the meeting hosts for the next 7 days, starting from today's date.  

With this example data, we would need to convert the day names to numbers to implement the sort. The formula would look like this.

ClearCollect(colMeetingHostsDays,
             AddColumns(
                colMeetingHosts,
                "DayNum",
                Switch(
                  DayName,
                  "Monday", 1,
                  "Tuesday", 2,
                  "Wednesday", 3,
                  "Thursday", 4,
                  "Friday", 5,
                  "Saturday", 6,
                  "Sunday", 7
                )
             )
)

This produces the following result:


We can then sort the result like so.

Sort(
    colMeetingHostsDays,
    If(DayNum < Weekday(Today(), StartOfWeek.Monday), 
       "b" & Text(YourWeekNumCol), 
       "a" & Text(YourWeekNumCol)
     ),
    SortOrder.Ascending
)

The theory here is to build a calculated 'sort' column. Let's say that today is Wednesday. We would prefix days 3 and above with 'a', and days under 3 with 'b'. We can then carry out an 'alpha' sort to return the days in the desired sequence. 

The end result looks like this based on today's day (Wednesday). We can see that the result is sorted in the order of Wednesday, Thursday, Friday, Saturday, Sunday, Monday, and Tuesday. 


To clarify this further, this screenshot shows the calculated sort value to illustrate how the data is sorted.


How to apply circular sorting based on month name

To give another example, here's a list of public holidays for the year. 

ClearCollect(
  colPublicHolidays,
  {MonthName: "January", Holiday: "New Year's Day"},
  {MonthName: "January", Holiday: "Martin Luther King Jr. Day"},
  {MonthName: "February", Holiday: "Presidents' Day"},
  {MonthName: "March", Holiday: ""},
  {MonthName: "April", Holiday: "Good Friday"},
  {MonthName: "April", Holiday: "Easter"},
  {MonthName: "May", Holiday: "Memorial Day"},
  {MonthName: "June", Holiday: ""},
  {MonthName: "July", Holiday: "Independence Day"},
  {MonthName: "August", Holiday: ""},
  {MonthName: "September", Holiday: "Labor Day"},
  {MonthName: "October", Holiday: "Columbus Day"},
  {MonthName: "November", Holiday: "Veterans Day"},
  {MonthName: "November", Holiday: "Thanksgiving Day"},
  {MonthName: "December", Holiday: "Christmas Day"}
)

To display the public holidays for the next 12 months starting with today's month, we would use this formula. 

Sort(
    colPublicHolidays,
    With({MonthNum:Month(DateValue("1 " & MonthName & " 2024"))},
      If(MonthNum < Month(Today()), 
         "b" & Text(MonthNum, "00"), 
         "a" & Text(MonthNum, "00")
      )
    ),
    SortOrder.Ascending    
)

Again, we sort by a calculated 'sort' column. 

We use the technique here to convert the month names (eg January, February) to month numbers.

When we create the calculated sort column, note how we need to apply a Text format of "00". This ensures that the month numbers 1-9 are formatted as 01-09 to ensure that the sort takes account of months 10,11, and 12 correctly.

The final result looks like this based on today's month (February). We can see here how the January records appear at the end of the list.

Conclusion

To implement circular sorting, we can call the Sort function and sort by a calculated expression. This post provided two examples of how to implement this type of sorting.
  •   Categories: 
  • data
Previous
Next
Related posts