HR Anniversary Metrics

[dono2056]

I have a table containing a row for each employee, and another table that contains the hire date (and if they have one) a termination date.  I need to create a report that aggregates the time they have been employed, and reports on anniversaries in 5 year intervals, using hire date as a base, and today or this week as the dynamic end.   Additionally a 'nice to have' would be to include employees that have been terminated and rehired.  Any thoughts on how to go about this would be helpful.

[sidhiq91]

@dono2056  Could you please provide us with some sample data and also the expected output.

[Jebrezov]

Could you add a column to the load script or a master measure that is the calculation of today-hire date? That will give the number of days they have worked. Divide by 365 to get number of years (let’s call this yearsEmployed).
I think this value may be useful for other things, so I’d add another master measure to calculate the last 5year anniversary increment celebrated by doing floor(yearsemployed/5)*5. If years employed calculates to .5, this will evaluate to 0. If years employed calculates to 23.5, this will evaluate to 4.

this new column can be used for filtering or counting number of people in each time category.

I’m not sure what your format for the rehire situation is. A new line of info or more columns? Example data and results would help as @sidhiq91 mentioned.

[dono2056]

I had more time to think on this, so I should have been clearer on my expected output.  Here is a better picture of what I am trying to figure out. 

 I have a table, with columns that include employeeID(######), EmployeeDate (i.e. 01/02/2000), and EmployeeDateType (H or T).
An Employee can have any number of date (rows) assigned to it, ###### | 01/02/2000 | T -> ###### | 01/02/2001 | H for example, with EmployeeDateType = H (Hire) and T (Terminate), so multiple rows per employeeID, but not necessarily in order.
They can often be hired and terminated at least a few times. I need to sort by date (I assume), take each date group in chronological order (Each H and T as they appear in time), count the number of days that fall in that period, and then count each group of H->T in total to determine how many days they have worked at the company (per each employee) An Employee might still be active ( have no T Date), so I need to keep a running total to either today or the start of the most recent year (i.e. 1/1/2023). 

I assume this should be done in the model, or a combination.  Are there any specific functions I should be using for this, if this is possible at all?

[Jebrezov · Accepted solution]

my initial thought would be to reformat the data so there is 1 line per employee for each Hire to Termination  date and to as I've suggested in my previous post. does the anniversary count reset if the person is terminated and rehired? if it does not, then this would just mean you can sum up the yearsEmployed value across multiple rows to get their total. If it does reset, do you still want to display past employment, or would just the most current be what you are interested in? 

Either way, if you want to reformat the data into 1 line per employee for each hire to termination date, you'll want to sort it by employeeID and Date in descending order and use the Peek function to verify prior employeeid is the same as the current row, the prior row has a EmployeeDateType of T, and the current EmployeeDateType is H. if this is not the case, you can insert todays date for the running termination date. Then you can remove all rows with a EmployeeDateType of T and even remove the EmployeeDateType column entirely. 

This link has an example of the Peek function and also uses an alternate Previous function which may be of use as well. I can give more details, but want to give you the option of working through if you would like.

https://community.qlik.com/t5/New-to-Qlik-Sense/How-to-Create-Start-amp-End-Date-using-Date-Field/td...