How to get penultimate date in a sorted sequence of >1000 dates


I have sequences of transaction dates by buyer over a long period of time.  To classify the buyers properly, I need three of those dates:  the first date, the last date, and the penultimate (second to last) date.  I can't use a List because of the 1,000 item max list size--there are buyers with more than 1,000 transaction dates.

I can use GROUPFIRST and GROUPLAST (side note: I am worried these are just convenience functions that are under-girded by a List.  If true, GROUPLAST has a bug AFAIC).  How can I get the second-to-last value in the sequence?

Steve Bernstein


  • Avatar
    Alan Mark

    Hi Steve,

    You should be able to use GROUPBOTTOMN to do this.

    Selects the bottom N values from a group. If this function is applied on a date column, bottom N means the N least recent dates.

    So to get the last two dates, you would just use an argument of 2, allowing you to get both the last date and penultimate date in a single go.


    Comment actions Permalink
Please sign in to leave a comment.