How to calculate end dates in transaction list using next row's effective date (grouped by employee)

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

Hi, ​@JDLove, thanks for the reply!

I agree with the logic and have been trying something similar, but I’m struggling to make it work in Pigment. I’m not sure it’s just a syntax problem or if it can’t be achieved. 

This is the most refined I could get when translating this to Pigment’s syntax, but it’s always returning FALSE (i.e. Day_Today[remove first: Import]:

IF(
ISDEFINED(
_EE_EmploymentStatus_Historical.'Effective Date'
[FILTER: _EE_EmploymentStatus_Historical.Employee_ID = _EE_EmploymentStatus_Historical.Employee_ID AND
_EE_EmploymentStatus_Historical.Index = (_EE_EmploymentStatus_Historical.Index + 1
)]),

_EE_EmploymentStatus_Historical.'Effective Date'
[FILTER: _EE_EmploymentStatus_Historical.Employee_ID = _EE_EmploymentStatus_Historical.Employee_ID AND
_EE_EmploymentStatus_Historical.Index = (_EE_EmploymentStatus_Historical.Index + 1)],

Day_Today[remove first: Import]
)

Quick update: I just ended up pre-processing the dataset in Python, I couldn’t make the above work directly in Pigment.

Thank you, ​@JDLove! I’ll give it a try.