second last date

[veles]

Hello,

I have a date field holding dates of activities {S_CASE.ASGN_DT} for clients {S_ORG_EXT.DUNS_NUM}.

I understand to get the latest date showing is to use

{S_CASE.ASGN_DT} = maximum({S_CASE.ASGN_DT},{S_ORG_EXT.DUNS_NUM})

Is there a formula to get the second last date?

Thanks

 

[lbass]

You could insert a group (#2) on the date field. Then insert a maximum on the date field at the date group level. This will allow you to use a group sort. Suppress the group #2 header and footer. Go to report->group sort and select maximum of date and choose TopN instead of 'All' and set TopN to 2. This will return the two most recent dates per duns_num group (#1). If you only want to show the second most recent date per group #1, then go to report->selection formula->GROUP and enter:

{S_CASE.ASGN_DT} <> maximum({S_CASE.ASGN_DT},{S_ORG_EXT.DUNS_NUM})

-LB