Hello, I would like to correct the below formula so I can have the team of each employee by month based on his entry date and exit date with no time limitation.
The way the formula is currently it does not take into account the fact that I have 2 lines in my transaction list for an internal mobility. It only take into account the latest subteams. Here is an example: ‘Dataload HR tool - Employee.’ is my transaction list with all the info by employee.
Currently for this employee, i have the following result. But it should be “Finance & Planning” team until jan 24 and from Feb 24 until the end “Product data”
The data will be updated each month on the latest version of the ‘Dataload HR tool- employee’.
Thanks for your help
Page 1 / 1
Hi Cedric,
It’s tough with your dataset that does could indicate overlapping data - but I’ve tried to make a formula that works. Let’s consider my transaction DataLoad below
I have built two examples of employees having two contracts there.
In that case, I’m using the PRORATA function before doing the aggregation on the employee - the part that does PRORATA(Month, DataLoad.'Hire Date', DataLoad.'Term Date') is technically by DataLoad and Month, which you can’t displayed in a Formula Playground because its structure is a Transaction, but it is a very useful step. We end up with one line by Month, with a value on each of the Month each of the individual line is active on. If you really need to investigate this step and try it in a formula playground, you can temporarily turn your transaction in a dimension in its settings, after adding a unique ID (could be blank or Integer > auto incremented).
After that step, we can do a aBY LASTNONBLANK: DataLoad.ID] to aggregate each of the transaction lines on the Employees.
I have added a sorting option on the LASTNONBLANK with LBY LASTNONBLANK ON DataLoad.'Hire Date': DataLoad.ID] to make sure that on each Month, we take the most recent line in conflicting cases.
Please note that this approach does not use PREVIOUS, which could be beneficial in very large datasets / in places it’s hard to scope and make efficient. In your case, I think it fits perfectly well, including on Months were the data could be conflictual.
Have you had a look at our Workforce Planning Standard Application? It contains a lot of best-practices that could be interesting in your case I believe.
If you can, I highly recommend you import the different contracts as separate items though, as it will make your life much, much easier overall. That means you’ll be able to independently assign a hire date and term date to the different items, on top of being able to track their metadata.
If you later need to track and follow the data on individual employees, you can always aggregate the data as shown above.
The source data from the HR system usually evolves over time, so this would make sure you keep a strong dataset without any risk for issues. If you can pull a different employee ID, a contract number, a position identifier or any kind of tracker to separate these mobilities it will certainly make everything cleaner, faster and more maintainable.
