I have combed through the forums and have tried many of the solutions out there and seem to always end up hitting a road block. I am using Tableau 10.5 Desktop.
This is what I am attempting to accomplish:
- Calculate Average Headcount over a user-defined period of fiscal years (note: start of fiscal year is March). I am not opposed to using parameters to pass in start and end years to define the period of time in question.
- I am required to calculate additional metrics that will leverage the Average Headcount calcs above. As an example, I would also be required to calculate 'Promotion Rate', which would simply be (# of Promotions for fiscal year i / average headcount for fiscal year i). So example, FY 2010 Promotion Rate = X; FY 2011 Promotion Rate = Y; FY 2012 Promotion Rate = Z... and so on.
Additional data info (see attached sample spreadsheet):
Each employee has a unique [Employee Number]. For each Employee, every event, such as promotion, job title change, transfer, rehire, termination(s) etc get captured and an [Effective Date] (when this event becomes effective) gets labelled with each event. Along with each event, the employee gets a [Employee Status] assigned (Active, Terminated). Further, the system creates a date/time stamp [EjhDateTimeCreated] when that event gets created within the system. Unfortunately, for some Employees, they have multiple events that occur on the same day (date/time is unreliable here for sequencing), so an assumption is made that the record with the max. system created date (for each Effective Date) should be taken as the last event to occur on said day.
Keep in mind:
- each employee has gaps in their effective dates records (ie. they do not have an event that takes place every fiscal year)
- while I have an Original Hire Date and Termination date, employees can and do quit and come back - thus, multiple termination dates for some employees.
- i have about 2500 employees each who have a # of effective dates (anywhere between 1 and 20), so cross joins may be impractical.
Please see attached sample. Any help is much appreciated. Thanks.
Sample HC Data.xlsx 13.8 KB