I have a transaction list with a roster from each month taken on the last day of the month. Sometimes HR would make retroactive changes after the load is done. For example, HR may input a term date on July 1 for someone who terminated June 30. The term date now shows up in the July roster but is missing from June roster. I want to somehow flag that the June roster is MISSING this term date.
In the image, Vincent’s term date was captured in July because the term info was processed AFTER the June date was imported. Vincent should have a June term date in the June snapshot, but it is missing instead.
This essentially looks up the the employee’s Term month using the snapshot month that is = to the term month (in “Test1”). The result here for Vincent would be blank since there is NO Term month Vincent’s June data. The end goal would to find these discrepancies so that I can go and fix source data (by adding a term date to Vincent’s June data)
Page 1 / 1
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:
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.
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:
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.
Hi,
I made it work in a separated metric using this formula:
Source list reproduction reflecting your use case:
Since Bernard already has a good Term dt in snapshot month, we don’t consider it and observe only the difference between snapshot month and term date month when there is no good Term date in snapshot month.
Please let me know if we made a step forward :D
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.
