Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
Newbie7
Partner - Creator II
Partner - Creator II

Employee tenure measure with department changes

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:

Newbie7_0-1633272719585.png

Newbie7_1-1633272902886.png

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.  

11 Replies
Newbie7
Partner - Creator II
Partner - Creator II
Author

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).  

Newbie7_0-1633524982770.png

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,

Newbie7
Partner - Creator II
Partner - Creator II
Author

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.  

Newbie7_0-1633787590731.png

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

Newbie7_1-1633787682224.png

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
Employee
Employee

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

Regards,
Mike Tarallo
Qlik
Newbie7
Partner - Creator II
Partner - Creator II
Author

Thanks a lot Mike.  I greatly appreciate it!

Best regards,

Sakiko

hic
Employee
Employee

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. 

Henric_Cronstrm_0-1634050361512.png

 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

Newbie7
Partner - Creator II
Partner - Creator II
Author

Hi 

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

hic
Employee
Employee

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.

Newbie7
Partner - Creator II
Partner - Creator II
Author

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

Newbie7
Partner - Creator II
Partner - Creator II
Author

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