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

Follow

Steve Bernstein

• January 16, 2018 20:03

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?

0

• 

  Alan Mark

  • January 16, 2018 20:13

  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

  Comment actions Permalink
• 

  Steve Bernstein

  • January 16, 2018 20:29

  That's good--that'll work, thanks!

  0

  Comment actions Permalink

Please sign in to leave a comment.