I have a single table of employee data, that contains every employee, and all of their employment history (so multiple rows per person in many cases, for each change in role, grade etc) with the start and end dates of the changes to their employment (position start date, position end date).
I need to find a way of calculating what the headcount was on any given date, so that I can for example graph headcount over time - I have created a calculated field that can do this for one specific date:
If
[position_date_from] <= TODAY()
AND
[position_date_to]>= TODAY()
THEN
[main_position_flag]
ELSE
0
END
But what I really need is for date bit to be dynamic - so that I can return the headcount at any given month end say, without having to create a whole new calculated field for each month end. I have tried adding a calendar table to my data, and suspect that somehow that may be the answer, but I have not been able to get that to work.
I've added a dummy data set to show the basic format of the data - for someone to be considered 'active' on any given date their position start date must be less than or equal to the selected date, and their position end date must be greater than or equal to the current date.
Any help you can offer would be much appreciated.
Thanks
Ed
@Ed Uff Check out Displaying Active Records Over Time. I recommend options 1 or 2, which both restructure the data so that every employee has at least one record per month from when they started a position to when they stopped. There is also a calculations only option.
Please @ me in replies otherwise I won't receive a notification. Vote for more notification options
Hope this helps :)
What do you think of the new Community Forums? Let us know!
(Be sure to tag your ideas as 'community')
