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.

Hi Sorry for late reply,

I have made a quick prototype to test a new approach as I also could not get the initial code working.

Blocks 2

1. Transaction List called “data”

Properties:

Employee is a dimension type to Employee dim

Index = RANK(data.EffectiveDate, data.Employee, ASC)

PreviousIndex IF(data.Index = 1,ITEM(1,Sequence.'Sequence Number') ,ITEM(data.Index-1,Sequence.'Sequence Number'))

NextEffectiveDate = IFDEFINED ( data.Index, DateLookup[BY: data.Employee,data.PreviousIndex],CurrentDate)

2. Helper Metric called “DateLookup”

2 Dims ( Employee, Sequence )

Sequence dimension is just a list to hold the sequence date for each Employee

Formula = data.EffectiveDate[BY FIRSTNONBLANK: data.Employee , data.PreviousIndex]

Concept was in these steps

1 Rank the Employee transactions (Index)

2 calculate the previous rank number (PreviousIndex)

3 Use helper Metric to pass the PreviousIndex Date (NextEffectiveDate)

Seems to work if alittle basic, I hope it helps and you can improve on it. I had hoped to upload screenshots but I was unable to upload any images tonight.

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