Hello All,
I am unsure on how to calculte the turnover percentage from the below table. Below is an example of how my data look like in database. Everyday a snapshot of data is taken and is stored in a data warehouse. Each date when the snapshot is taken is stored in the effective date column.
Emplid
| TerminationDate
| Gender
| EffectiveDate
|
|---|
| 1 | 4/25/2006 | M | 9/28/2006 |
| 2 | - | M | 9/28/2006 |
| 3 | 8/28/2005 | F | 9/28/2006 |
| 4 | - | F | 9/28/2006 |
| 5 | - | M | 9/28/2006 |
| 1 | 4/25/2006 | M | 10/28/2006 |
| 2 | - | M | 10/28/2006 |
| 3 | 8/28/2005 | F | 10/28/2006 |
| 4 | 9/28/2006 | F | 10/28/2006 |
| 5 | - | M | 10/28/2006 |
| 6 | - | F | 10/28/2006 |
|
|
| 10/28/2007 |
|
|
| 10/28/2007 |
|
|
| 10/28/2007 |
|
|
| 10/28/2007 |
|
|
| 10/28/2007 |
|
|
| 10/28/2007 |
|
|
| 10/28/2007 |
As, you can see on each date the data keeps on changing (new employees are added and some eployees left the company. I have to use :
Dimension as FY-06, FY-07, etc...where FY-06 corresponds to a specific date range..eg from ,23sep,05 to 28 , oct, 06.
second dimension as Gender.
Expression is something I am facing problem. I have a created a daterange using interval match for FY-06, FY-07 etc.
Turover for 06 would be:
%= count(distinct(all employees in Fy -06, where TermDate is not NULL) /
((count(for the date range for FY-06(the total employees based on male & female seperately)/ total count distinct date (date when snapshots are taken in database) for FY-06))
this is for fy-06. So the formula should be generalized and should calcuate for each year and display it on bar graph.
I used something like this:
count(isnull(termdate=0,Emplid))/((count(Total<daterange, gender>, emplid)/ X)
X- should vary distinctly. Like suppose if we have 300 days of which we are taking data in account for FY-06, it should return 300 and so on for FY-07, FY-08, FY-09 etc.
how to add a denominator that can calculate count of total distinct date for each year??
this is for fy-06. So the formula should be generalized and should calcuate for each year and display it on bar graph.
I am facing hard time in finding a solution to this. Please help me out.
Thanks .
