4 Replies Latest reply on Oct 17, 2018 7:23 AM by Shinichiro Murakami

# Overlapping date ranges - HR Analytics

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 !

Stefanie

• ###### 1. Re: Overlapping date ranges - HR Analytics

HI Stefanie,

Hope this helps.

The Very first step you need to do is Pivot.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Overlapping date ranges - HR Analytics

Following up.

Thanks,

Shin

• ###### 3. Re: Overlapping date ranges - HR Analytics

Hi Shin,

I am sorry for the delay in my reaction. I didn’t expect to get an answer so fast !

Thank you this was indeed helpful as I had not yet discovered the pivot function in Tableau.

I was able to select the pivot on the data of the anonymized twbx but I don’t get this option in my real data. Hire Date and Termination Date are selected and both fields are discrete dates.

As the data comes through a Custom SQL Query from Tableau Server I tried in Live Connection and as an Extract, also saved as an twbx, but I still don’t get the pivot option.

Kind regards,

Stefanie

• ###### 4. Re: Overlapping date ranges - HR Analytics

HI Stepahnie,

Typically, the answer is posed in 10 min~2 hours.

Anyways, Pivot is available in limited connection, and from your description, you need to consider the modification of Custom Query,

or even the stage before publishing the connection to Tableau server.

Thanks,

Shin

1 of 1 people found this helpful