1 Reply Latest reply on Nov 21, 2012 11:33 PM by Dean Hewitt

    How do you create a line trend of a count or rate of all records where the date trended falls between a start and end date?

    Alan Bainbridge

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

       

      Usually I use a query that returns 1 line for each department and Month with the total count of employees and the number of terminations.

       

      The disadvantes of this are that I'm stuck with the date increment and aggregation I choose in my SQL query (Month, quarter, week, by department, by job-title, etc.).  I also think it's inefficient and a waste of storage to count each employee in each time period when I could just have a start and end date for each employee.

      I want to create monthly (or weekly, quarterly) turnover rates with just a start date and end date using Tableau's date-drilldowns.

       

      Essentially the problem is getting Tableau to chart a date and count/sum any records where the hire date is before or on the date increment (whether day, month, year) and the termination date or Max-date is on or after the date increment.

       

       

      In the attached worksheet there are two datasets, both use the SuperStore data (modified for my purposes). 

       

      • The data set "Customer Churn with MIN MAX dates by customer" has the data structured the way I want it (but I'm unable to create the VIZ)

      I created a "Date of First Order" field.  This is similar to an employee's hire date.  It is the minimum order-date for each customer.

      The "Min-Date" field is '1/1/2010' for most customers unless their first order was later (but before 2012).

      The "Max-Date" field is the date of the customer last order, or '12/31/2011'  if the last order was in 2012. (Anyone with the last order in 2009 was excluded)

       

      I limited the data to 2010 and 2011 because this makes it look more like employee turnover data where each report has a large group who've been on-board for years.  And another large group who have not terminated and continue on after the end of the date range.

       

      • The data set "Individual Dates with count of Customers and Lost Customers" has the data structured by day with a count of total, new and lost customers.  I have a rudimentary chart called "Desired Viz" to show what I'm looking to create with the other dataset.

           

         

        The "individual dates..." dataset may seem fine, but when you multiply these dates over several years and have one row for 2000+ departments for each date it's a question of a few million rows or <100K rows (one for each employee).

         

         

        I am wondering if a table calculation or running_sum or running_avg is the answer?

         

         

        Thanks!

        I previously posted this question here:

        http://community.tableau.com/message/190236#190236

          • 1. Re: How do you create a line trend of a count or rate of all records where the date trended falls between a start and end date?
            Dean Hewitt

            Hello Alan,

             

            To generate the metrics you want the data set needs to have an entry for each employee for each day they worked.  This can be done with two sets of data, one you extract from your data base and another which is a list of dates covering the days in question.

             

            The employee file will have the following data:

            Employee ID:

            Start Date:  The first day of work for the employee.  Granularity is at the day level.

            End Date:  The last day of work for the employee.  If the employee is still working, then this is blank. Granularity is at the day level.

            JOIN End Date:  The last day of work for the employee.  If the employee is still working, this this contains a date in the future such as 1/1/2099.  This field is only used for joining the two data sets.  Granularity is at the day level.

            <Dimensionaly data: Dept, gender, birth date, job-title, etc>

             

            The date file has a single field: Date.  The first entry is equal to the min date in the employee file.  There is a single entry for each day.  The last entry is the last day of the employee file, or the current date. 

             

            There are two joins:

            Date >= Start Date

            Date <= JOIN End Date

             

            You must include Date in your data record, as this is the date you will use for all you trending.  You do not need to include JOIN End Date, at this is only used for the join. 

             

            This will creata a record for each employee for each day they have worked.  You need to use the Date field when generating any trend lines, etc.  I will post an example for you. 

             

            Regards,

            Dean