Employee tenure measure with department changes

[Newbie777]

Hello,

I am trying to write an employee tenure measure which deals with tenure at any point in time. A particular difficulty I am facing currently is due to the fact that many employees change departments multiple times, and some employees leave the company and come back at a later date, and because of this, employee database has multiple hiring and leaving date for a single employee, so instead of normal tenure where I just have to deduct hiring date from the leaving date, I need to be able to calculate the tenure which correctly deals with multiple hiring and leaving dates.  As an example, the visualization below is an employee tenure of one employee who changed department in January 2018.  Even through her tenure should be continuing from December 2017, it restarts the counting of tenures in January 2018.  However, I want it to be continuation of before the department change rather than starting the counting fresh in January 2018.  The measure I wrote for this is as follows:

How can I tweak the measure formula so that the result of the calculation will be continuing even after the department changes?  

Thanks for your help.  

[Newbie777]

Alternative to the measure above, when no filtering is applied for the time dimension, the measure below calculates the correct tenure which doesn't restart the tenure accumulation when department change happens.  However, the problem with the below measure is that when filtering is applied to the time dimension, the calculated result is all uniformly divided, and just calculates the tenure of the month selected (i.e., 30 days).  

I suppose I need the hybrid of the two measures above, which is able to deal with flexible time dimension selection, while at the same time, being able to deal with department change tenure accumulation correctly.  

I'd greatly appreciate expert help on this issue.  

Thank you & best regards,

[Newbie777]

I've been thinking about this issue further, and more simply, I have the employee measure which calculates correct month end number of employees at any time from the employee database which have multiple hiring and leaving dates for department changes etc., and I thought I can possibly use the measure below to calculate the tenure more simply by properly accumulating the headcount.  

The visualization below is the result of filtering to one employee who once left the company and then came back at a later date.  

I would like to incorporate the running total formula to the measure above so that tenures will be calculated regardless of employee department changes and leaving and coming back to the company.  Please could you let me know, how I can incorporate running total formula to the measure above?  

I tried RangeSum(Above(Sum(Amount),0,RowNo())) function, but it is adding up for everyone from the start of the calendar table, and not producing the desired outcome.  I'd greatly appreciate experts' help with this.  

Thank a lot in advance.  

[Michael_Tarallo]

Hi Sakiko - I reached out to a member of our team, hoping he can reply to you. 🙂

[Newbie777]

Thanks a lot Mike.  I greatly appreciate it!

Best regards,

Sakiko

[hic]

It all depends on how the data model looks...

For example, if you have a table that contains "employments" with a hire date and a leave date, and an employee that has multiple hire dates also has multiple records in this table, e.g. 

 then you can calculate the tenure (in days) simply by using

Sum(Leave-Hire+1)

Is it possible to create such a table from the data you have?

Also, I would avoid an Aggr(), if possible.

HIC

[Newbie777]

Hi Henric_Cronström,

Thanks for your response.  Yes.  I can create such data table, but I think the problem with that approach is that I probably will lose the flexibility with respect to the time dimension.  Is this assumption correct?  Because the tenure will be calculated in a row by row table at a specific point in time as a reference date, which will not be able to convert to any time period tenure.  For example, a person who joined 3 years ago, will have 3 years of tenure as of today, but 2 years of tenure 1 year ago, and zero tenure at 5 years ago and so on.  If I fix tenure in the employee fact table, would I still be able to deal with tenure at any time dimension flexibly?  I would imagine if it is a virtual table created by a measure, it may be more flexible, but with the real data table with fixed tenure field, I am afraid may not be able to flexibly deal with any time dimension selection.  Please let me know if my concern is not warranted. Also, I'd greatly appreciate it if could let me know why you are not in favor or using aggr().  Is it due to performance issue (slowness of calculation)?  

Thanks a lot for your advice.  

Newbie7

PREVIEW

[hic]

If you want to select a date, and see what tenure the employee had at that date, you can always find a way around to do this.

One possibility would be to generate all combinations of days and employees, see https://community.qlik.com/t5/Qlik-Design-Blog/Creating-Reference-Dates-for-Intervals/ba-p/1463944.
Example:

Employments:
Load
EmploymentID, EmployeeID, FromDate, ToDate
From Employments;

Employments_x_Dates:
Load
EmploymentID,
FromDate + IterNo() - 1 as EmployedDate,
FromDate + IterNo() - 1 as ReferenceDate
Resident Employments
While FromDate + IterNo() - 1 <= ToDate ;

Link ReferenceDate to your calendar and use it as selection field.

Then the tenure will be
Count(EmployedDate)

and if you want to use the selection as a cut-off, you can use set analysis:
Count({<ReferenceDate={"<=$(=Max(ReferenceDate))"}>} EmployedDate)

HIC

PS Aggr() is cpu-intensive, so with a lot of data it affects performance. Sometimes you need it, and then you should use it. But if you can find a solution without Aggr(), it is usually faster.

[Newbie777]

Thanks HIC for taking time to answer my questions.  I will look into that solution, but my impression is that as I have over 20 years of data with 300 rows of employee fact table, the possible combination of the data generated would be at least 200 * 365 * 300 = 2,190,000 rows of data, which is almost twice as big as number of rows available in an Excel sheet; and I am not sure if 2.1 million rows of data will be quicker than 300 rows of fact table with aggr().  FYI, at the moment, in terms of headcount measure with aggr(), the calculation is pretty quick and I never had to wait while it calculates, although tenure measure is somewhat slower even though it is totally acceptable in terms of speed (as my PC has quite decent sized RAM and CPU on it).  I'd also greatly appreciate it if you could let me know the related question I raised in another topic if it is quick to answer for you.

How to write rangesum expression for correct tenur... - Qlik Community - 1844552

Thanks a lot for your time in answering this question.  

Best regards,

Newbie7

[Newbie777]

This has been solved subsequently.  Please refer to the remaining issue below.