Perform an XLookup Equivalent in Transaction list

Hi, 

I can see you already have a boolean check in your list: ‘Term Dt in Snapshot Mo’

I believe you can just replicate this logic.

I made a small reproduction.

I created a new boolean property ‘Term Dt not in Snapshot Mo’ using the formula:

if('Transaction List'.'Snapshot Month'<>'Transaction List'.'Test1 (Term Month)', true)

that just compares the snapshot month with the term month.

Then you can filter the list to only display discrepencies that you need to act on.

That can generate a board with your list + the discrepency view:

But to go further, you can even create a metric using this flag, See below, I created a Boolean metric dimensioned by Employee and Month that flags when a term date fix is required.

Formula is

if('Transaction List'.'Snapshot Month'='Transaction List'.'Test1 (Term Month)', true )

[by lastnonblank: 'Transaction List'.'Employee Name', 'Transaction List'.'Snapshot Month']

or can reuse the boolean created above:

'Transaction List'.'Term Dt not in Snapshot Mo' [by lastnonblank: 'Transaction List'.'Employee Name', 'Transaction List'.'Snapshot Month']

What is great by using the separated metric approach is that you can create a notification and even a task for your HR resp.

Go to Automations > Create an Automation

And create an automation that triggers a task when the Term date fix required metric is updated.

This notifies you in Pigment and by email that you have a task to do.

Finally, one note on the Xlookup topic as it’s a different logic in Pigment.

I recommend you to read this article: 

Hope this helps.

Best,

Hi Benoit,

Thanks for the comment and suggestion on automation of notifications. I will definitely use that. 
 

I tried your approach and while it does work when the person is a NEW term, it doesn’t quite work when we have many months of data. We snapshot our HRIS data every month and an employees status is shown in every load. In the image below, you’ll see that “Lindsay” is term that happened a long time ago. She should not show up because her FIRST instance of a termed date was recorded correctly in Jun 23. I only want to see any snapshot month where the first first occurrence of a term date is incorrect.

Hey,

Can you try this formula in your Term Dt not in Snapshot Mo list property:

if('Transaction List'.'Test1 (Term Month)'= ('Transaction List'.'Snapshot Month'-1), true)

Assuming, the difference only exists when the term month is inferior by 1 from the snapshot month.

Please let me know.

Best,

So close but not quite! The formula still shows TRUE for the month after the load but in this case, since the term was correctly reflected in June (term month = snapshot month) then the result should be blank and not true for the July 23 snapshot row.

Is it possible to reference the snapshot month prior to the row’s snapshot month? Something to the effect of -

If (('Transaction List'.'Snapshot Month'-1) = (‘Test1 (Term Month)’ -1), Blank

else formula you provided.

This solution works! Thank you!

Nice!

By the way, I returned boolean on my formula (which is perfect if you want to set up an Automation tasks to notify you about every new discrepency.

But you can also return a date, which makes the discrepency more visible.

Have fun modeling this process with Pigment!

Best,