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]

Hi HIC,

I copied and pasted your script above to the data load editor, and brought in the table to the data model.  

The Employments_x_Dates table is as shown below and before I tried to convert field type to date format, it was showing same as the next column on the right which is ReferenceDate field.  Although the data load script did not generate error, and I successfully created the tables, the data field type selection will show all the values as null.  I'd appreciate it if you could let me know where I am going wrong with this.  

Also, in the script below, EmployedDate and ReferenceDate are exactly the same, but is this OK?  

Regarding the number of rows, I kind of understood that Excel row numbers and Qlik Sense row numbers are not comparable in terms of data processing speed and efficiency as Qlik Sense calculations can only operate on columns (i.e., fields) and hence more efficient in processing big data, while Excel formulas are applied to cells and consequently use up more memory.  

Thanks a lot for your help.

[Newbie777]

Hi HIC,

Thanks for your advice.  I've adopted the load script approach as you recommended rather than aggr approach, and I got as far as the point where I got into the issue with aggr, in the sense that general calculation is OK, but due to the complexity of the employee fact table which I want to visualize, which contains multiple department changes, and leaving once and coming back, I am facing the similar issue of not able to visualize correctly those two cases.  For the employees who changed departments, instead of accumulating the tenure at the company despite the change in departments, the visualization seems to start fresh the accumulation when rangesum is used to accumulate the results. Also, for the employee who left the company and then who came back, when accumulation by rangesum is used, the gap period does not appear.  When the rangesum is not used as suggested by your original measure, month by month tenures are truncated and not accumulated as shown below.  So at the moment, although the solution is a fresh approach away from aggr, when it comes to dealing with the intricacies of the fact table irregularity, I am still faced with the same problem.  I'd greatly appreciate it if you could let me know how I can solve these issues with respect to correct accumulation for employee department changes, and gap period visualization for employee who once left the company and who came back.  

Thanks a lot for your advice.