Skip to main content

In a transaction list sorted by employee and effective date, how do I create a property that shows the next row's effective date for the same employee (or TODAY() if it's the last row)?"

Hi 

I would try something like this but its just an idea I have not tested this myself!

  • Create an Index property using RANK(Transactions.'Effective Date', Transactions.Employee, ASC).
  • Ensure the list is sorted by Employee, then Effective Date
  • Add a Next Effective Date (Date) property with formula:

IF(

       ISDEFINED(SELECT(Transactions.'Effective Date',

              FILTER(Transactions, Transactions.Employee = Transactions.Employee

              AND Transactions.Index = Transactions.Index + 1))),

      SELECT(Transactions.'Effective Date',

            FILTER(Transactions, Transactions.Employee = Transactions.Employee

            AND Transactions.Index = Transactions.Index + 1)),

      TODAY()).

 

Regards

JDLove

Reply


Ask your question here!

Terms & ConditionsCookie settings