2 Replies Latest reply on Nov 19, 2012 4:10 PM by Alan Bainbridge

    Trending a percentage by date using a date range for each row in my data.

    Alan Bainbridge

      I work with HR data and I want to be able to trend turnover rates.

       

      Currently my query defines the date increments by month and pulls the count of terminated employees and the count of all employees for each month and year.  This works fine if I only want to trend by month and year.

       

      I'd like the flexibility to do something more dynamic and use Tableaus relative date filtering.

      I want to be able to set up parameters to change the date trending to year, quarter, month, week, or day units.

       

      To accomplish this I've written a Custom SQL query that pulls one row per employee with a start-date, max-date and flags for different types of terminations.

       

      The numerator for the rate is easy.  terminated employees are flagged in the data and I have a date of termination.

       

      The denominator is the problem.  Each employee has a start-date and a max-date.  The max-date is today's date if they are still employed or their last date of employment.  (and flags to differentiate retirees, voluntary, involuntary termination etc.)

       

      The heart of the problem:  If an employee starts in Jan 2010 and terminates in Apr 2011.  That employee would need to be counted for all the months (or whatever date-units) between the start-date and max-date in the denominator for my rate. ( example: Jan/2010, Feb/2010, Mar/2010 ...... Mar/2011, & Apr 2011)   I also need to figure out how to include the terminated employees in ONLY the denominator of my rate in the months PRIOR to termination.

       

      Here's what I've tried:  I created an additional data-source that pulls all dates for the last 2 years.  I then tried to create a T/F field where the field is true if the date is between the start-date and max-date from my turnover data.  But this didn't work.  I think this is because the relationship between the connections wants to identify that one field EQUALS another.  Not that another field is BETWEEN two fields.

       

      example of data:

      Person_IDStart_DateMax_DateTerminatedTerm_1YRTerm_90Days
      1112/5/20015/16/2012YNN
      2223/9/20125/7/2012YYY
      33310/23/201012/30/2010YYY
      4446/8/20114/23/2012YYN
      5553/26/201211/30/2012NNN
      6663/15/19977/18/2011N (Retired)NN

      In this example I have dates from 2/5/2001 to 11/30/2012.  But only 12 unique date values.  How do I get Tableau to treat this as a date range and chart all relevant values?