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

2 followers
0
Avatar

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

2 comments

  • 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.

    Alan

    1
Please sign in to leave a comment.