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.
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).
1 of 1 people found this helpful
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.
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).
Thank you all for the ideas so far. I will definitely be trying them out and get back with you regarding my results.
1 of 1 people found this helpful
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.
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.
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.
((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()))))
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.
Did you ever find a solution to this? I'm wanting to do this exact thing as well.