1 Reply Latest reply on Oct 18, 2013 4:00 AM by Jim Wahl

    How do you create a cumulative employee turnover trend chart using only a start-date and end-date?

    Alan Bainbridge

      I've attached a twbx file created from the Superstore data to illustrate my question.


      The data contains two data sources (both created from Superstore data).

      1. Customers - (Sample Superstore ...  This one is a list with 1 customer per row (no duplicates).

      Each customer has a "Start Account Date" (analogous to a a hire date).  I derived it from the earliest Order Date in the data.

      There is also an "End Account Date" (analogous to a termination date).  I derived this from the latest Order Date.  I left them blank for anyone with an order after 7/1/2012 to represent customers who have not ended their accounts (analogous to employees who have not yet terminated.


      Monthly (Sample Superstore ...  I created this dataset manually from the 1st dataset.  It has 1 row for each month/year combination.  and the count of customers who ended their accounts and a count of active customers for that month.


      I successfully created my visualization from the 2nd dataset with monthly counts.  But I want to find a way to create such a trend chart from the 1st dataset.  A major part of the problem is that I can't figure out how to create an aggregate function that will sum my count of terminations or active employees for months BETWEEN their hire and termination date.


      I usually just write my query so that it gives me a count of terminations and employees for each month (as in the 2nd dataset).  But then it's difficult to use that same data and trend the data weekly, quarterly or yearly.  It also makes the query very long.


      Does anyone know how to create a trend using a dataset that only has start and end dates?

      I can create a running sum of hired employees.  And I can create a running sum of terminations.  But the difficult thing is combining these to get a turnover rate based on two different date fields.


      Thank you

        • 1. Re: How do you create a cumulative employee turnover trend chart using only a start-date and end-date?
          Jim Wahl

          Hi Alan,


          For me, the most straight forward way of doing cumulative difference calcs is by reshaping the data to have a row for start date and end date.

          2013-10-18 13-56-22.png


          For any particular period, the number of closed accounts / terminated employees is just:

          Closed Accounts =

          SUM(IIF([Date Type]=='End Account Date', 1, 0))


          And the number of current customers / active employees in any time period is:

          Customer Count =

          RUNNING_SUM(SUM(IIF([Date Type]=="Start Account Date", 1, 0))) - 
          RUNNING_SUM(IIF(FIRST()==0, 0, LOOKUP([Closed Accounts], -1)))


          And your cumulative turnover rate is the same formula as you used previously:

          running_sum([Closed Accounts])
          running_avg([Customer Count])


          I believe you can also do this without reshaping the data, but it's not trivial.


          The other technique I've seen used is a self blend, where you duplicate the data source and rename the End Date to Start Date in the duplicate source. Now you can use number of records in each to see the cumulative counts. The self blend method is a bit perilous, however, as you need to be aware of domain padding (you can have problems if the start dates in the primary don't align with end dates in the duplicate source). For these reasons and because it'll confuse anyone who's maintaining the workbook, I'm not a fan of this technique.


          I reshaped in the data in Excel, but you can of course do this with custom sql or create a view in your database, perhaps specifically for this view.