5 Replies Latest reply on Nov 18, 2016 1:36 PM by Michael Franz

    Time Series Headcount

    Michael Franz

      I have a table with the an employee and their hire date. Mine was Nov 2007. I would like a 1 to be counted for each day, month, year as long as I have NOT been terminated. So I am looking for a continuous line....... I can load a small file, but not sure how to extract it without the server login.


      The data is as simple as


      Name     Hire Date     Term Date

      ME          11/1/2007     <null>


      Pick a date, pick a range and I'll be 1 for each day, month and year being displayed.

        • 1. Re: Time Series Headcount
          Norbert Maijoor

          Hi Michael,


          Not sure what you are aiming for but find my approach as reference below and stored in attached workbook version 9.3



          a. Refdate: IFNULL([Term Date],today())

          b. Delta Ref date in days: DATEDIFF('day',[Hire Date],[Ref date])

          c. Delta Ref date in months: DATEDIFF('month',[Hire Date],[Ref date])

          d. Delta Ref date in months: DATEDIFF('year',[Hire Date],[Ref date])

          • 2. Re: Time Series Headcount
            Joe Oppelt

            If you want an actual string of 1s, you'll have to generate a string of rows in which to load the 1s.


            If you just want to calculate numbers, then check out what Norbert did.

            • 3. Re: Time Series Headcount
              Michael Franz

              So, in order to do this, I would have to have data that looked something like the following?? See attachéd???

              • 4. Re: Time Series Headcount
                Joshua Milligan



                Yes. Basically, you need one row per date (per person, if each row will count as 1 -- or you can roll it up).


                There are a few ways you can do this:

                1. Use Data Densification in Tableau to fill in missing dates and some table calculations to carry forward the 1 values and add them all up.  Pros: no need to adjust your source data. Cons: complex, brittle, hard to setup and maintain.  I would not recommend this approach if you can do either of the following.
                2. Create a data source that gives you a row per date (per person).  If you have the ability to write a custom script or cross join to table containing a row per date (or use Tableau 10's ability to cross-database join to an Excel file of dates - you'll have to include a value in both tables that give you a join on every row) then you'll have a data source that is fairly easy to use.  Pros: easy to use data source, drag and drop in Tableau.  Cons: little bit of setup, potentially untenable if you already have a large volume of data, because this will create a Cartesian product (but works well for smaller/medium sized data sets)
                3. Create a data source that contains every date and use that as your primary data source.  Then blend to your existing data.  Use table calcs to fill in values for dates between start and end.  Pros: keep existing data structure Cons: complex (not as much as #1), hard to work with filtering (because of the blend)


                #2 is probably the best approach (and is what you demonstrated with your Excel file).  But it's always good to know some options!


                Hope this helps!


                • 5. Re: Time Series Headcount
                  Michael Franz



                  Thank you for the help so far, but I see a few hurdles....


                  1. CountD will just provide me whole counts. But some people are not FT. So the CountD treats them as 1

                  2. If I just sum or count then that causes another situation....where each month is 1, but the year is 12 counts.


                  How do I fix this. See attached import of the data I presented in excel.....


                  Also, do I just create a "calendar" in excel of all the dates possible and join the calendar to the lines..... So for example, I've been here for 9 years, excluding leap days, 9*365 = 3,285 lines in the data set on the server. So if we have had exactly 200 employees employed for the last 20 years, I would have 200*365*20= 1,460,000 lines and thus adding 73,000 lines each year?? Or I could do monthly and have 9*12=108 lines for me.


                  Not a lot of data in the long run, but trying to get the concept.