2 Replies Latest reply on Oct 12, 2018 5:32 PM by Shinichiro Murakami

    Overlapping date ranges - HR Analytics

    Stefanie Gaudy

      Dear Forum members,

      I am struggling with overlapping date ranges, have consulted the James Wilson's question of 20 Dec 2016 as well as Wesley Magee's replies to it. I also spend lots of time to create sets and groups.

      Nevertheless, I am still not able to produce the evolution of attrition rate/integration success rate of a given population. I am attaching a Tableau anonymized workbook in version 2018.2.

      The definition of the attrition is "what is the %age of employees that joined the company over the last 18 months and who left during that same 18 months period ?".

      I started by creating one calculated field on a trailing 18 months period (TEM) for each 18 months period to define the joiners in that period :

       

      Joiners H2 FY18

      IF DATEDIFF('day',[Hire Date], DATEPARSE("DD/MM/YYYY","30/06/2018))<=546 THEN [Employee Number] END => should have 48 members

       

      Joiners H1 FY18

      IF DATEDIFF('day',[Hire Date], DATEPARSE("DD/MM/YYYY","31/12/2017))<=546 THEN [Employee Number] END => should have 99 members

       

      Joiners H2 FY17

      IF DATEDIFF('day',[Hire Date], DATEPARSE("DD/MM/YYYY","30/06/2017"))<=546 THEN [Employee Number] END => should have 139 members

       

      Joiners H1 FY17

      IF DATEDIFF('day',[Hire Date], DATEPARSE("DD/MM/YYYY","31/12/2016"))<=546 THEN [Employee Number] END => should have 152 members

       

      I understand that employees can be part of several TEMs as these periods overlap by 12 months with the next or previous TEMs and presume that this is the reason for my problem.

      Ideally, I would like to see the number of joiners in the different TEMs on the rows. The next step would then be to calculate and visualize the proportion of the employees in this TEM that have already left during that same period.

       

      Leaver H2 FY18

      IF DATEDIFF('day', DATEPARSE("DD/MM/YYYY","30/06/2018,[Termination Date]))<=546 THEN [Employee Number] END

       

      ………

       

       

      I would be very grateful if somebody could help me to find a solution. Looking forward reading you !

      Thanks in advance,

      Stefanie