9 Replies Latest reply on May 12, 2015 10:31 AM by Lynn Edwards

    Trending Age Of Workforce

    Adam Danisovszky

      Hello All,

       

      I am looking to trend my company's Age Of Workforce data for the current month and previous 12 months. I have no problems showing the current month, but I am completely at a loss as to how to approach calculating the previous 12 months.

       

      Here are some basics of what I am working with:

      • Employee Date of Birth
      • Employee First Hire Date
      • Employee Last Hire Date (for those who have been rehired)
      • Employee Last Termination Date
      • A field indicating whether the employee is currently active (A) or terminated (T)

       

      I know this is essentially a multi-step issue in that I:

      1. Need a way to calculate whether or not an employee was active on a given prior date
      2. Need to then sum up the active employees on that given date
      3. Show 1 & 2 over the prior 12 months (or any other periods of time desired by upper management)

       

      Here is an image of the pie I have created for the currently active employees. Ideally, the 12-month trend will show the percentages in consecutive stacked bars.

       

      Age Of Workforce Example.JPG

       

      Any help that any of you can provide will be greatly appreciated!

       

      -Adam

       

      *Note: due to this being private employee data, I cannot attach a packaged workbook.

        • 1. Re: Trending Age Of Workforce
          Ankit Patel

          Hi Adam,

           

          Seems like this can be easily done with Table Calculations? Have you looked into them? Also, I am not sure a Pie chart would be best way to show the difference but try it out as you never know it might just work for you.

           

          Best,

          Ankit

          • 2. Re: Trending Age Of Workforce
            Adam Danisovszky

            Thank you for your response, Ankit.

             

            I have looked briefly into Table Calculations, but I'm not sure how exactly they would apply in this case. I am not really looking for things in terms of how the totals relate to each other month-to-month, but rather with each month as its own individual "snapshot" of the workforce on the specified dates (most likely the first of the month).

            • 3. Re: Trending Age Of Workforce
              Darin Coulter

              It seems like your data set might be essentially 1 row per employee, so you have a snapshot "as of today" for every record.  I don't think you can get the time series trending analysis from a snapshot as of today - but there are really smart people out here and I will be interested to see what they come up with for you.

               

              My suggestion is...

               

              If your data source (SQL Server would for example) allows for it - you could set up a monthly stored procedure if your target evaluation period is 'every month'.  When that procedure runs you could get 1 record for every employee who was 'active' at some point during the evaluation period for multiple eval periods over time.  You could also generate 0/1 fact measures like 'Hired' if their last hire date is between the @min and @max evaluation dates... same idea for Terminations.  You could calculate Employee Age from DateOfBirth to @max evaluation date for active records or DateOfBirth to LastTermDate for employees terminated in the evaluation period.  You could then see how your AVG(Age) is trending...you could do something very similar for Tenure and see how the Tenure of your workforce is trending.

               

              Going really far back in time from your 'current snapshot' for this type of fact analysis would probably require some serious assumptions - but it's entirely possible to generate a sort of fact table like this on a monthly basis from your 'snapshot' source on the 1st of every month for the prior month.  The evaluation period dimension could be a date field as "11/01/2014" (or the @min eval date) and that gives you your time series you'd be looking for... OR maybe you have a data architecture team that can take the question and build something more robust - but I think you need the monthly 'evaluation' records to see the trends you want.

               

              Good luck,

              Darin

              1 of 1 people found this helpful
              • 4. Re: Trending Age Of Workforce
                Syed Khadir Ahmed

                Hi Adam,

                 

                Just check this attached Workbook.. This was my initial question when I joined forum.. and luckly Joe helped me out.. :-)

                 

                I am sure you should be in good position after using the calculations in the attached work book.. (hopefully).

                 

                Tableau Challange

                 

                Regards,

                Khadir.

                • 5. Re: Trending Age Of Workforce
                  Adam Danisovszky

                  Thank you all for the ideas so far. I will definitely be trying them out and get back with you regarding my results.

                  • 6. Re: Trending Age Of Workforce
                    mark.jurriesii

                    I think you're best off with Darin's approach, but if your data doesn't allow then you could create a parameter with multiple months in it, then set up table calcs off that. i.e. parametermonth - birthdate = age. This won't allow you to display the data in a trendline, but if you just need snapshots it should do the trick.

                    1 of 1 people found this helpful
                    • 7. Re: Trending Age Of Workforce
                      Adam Danisovszky

                      Thanks again to all of you for the input. I had some more pressing work land in my lap this morning, but I wanted to attach some sample data for any of you to tinker around with should your heart desire.

                       

                      -Adam

                      • 8. Re: Trending Age Of Workforce
                        Adam Danisovszky

                        So here is the formula I have previously used to calculate the headcount on a specific date, in this case 1 month prior to today. (Note: HR_STATUS can be either "I" for inactive or "A" for active.

                         

                        IF

                            ((ISNULL([LAST_TERM_DT])) AND ([LAST_HIRE_DT]<=(DATEADD('month',-1,TODAY()))))

                         

                            OR (([LAST_HIRE_DT]>[LAST_TERM_DT]) AND ([LAST_HIRE_DT]<=(DATEADD('month',-1,TODAY()))))

                         

                            OR (([HR_STATUS]="I") AND ([LAST_HIRE_DT]<=(DATEADD('month',-1,TODAY()))) AND ([LAST_TERM_DT]>(DATEADD('month',-1,TODAY()))))

                         

                        THEN "Active"

                         

                        ELSE "Not Active" END


                        Does this lend any insight for any of you as to how I might be able to approach things iteratively to tally for each of the previous 12 months without creating a separate Dimension for each of the 12 months then piecing them together into a dashboard?


                        Thanks again for any insight.


                        -Adam

                        • 9. Re: Trending Age Of Workforce
                          Lynn Edwards

                          Did you ever find a solution to this?  I'm wanting to do this exact thing as well.