Help needed in writing this expression!!

Thu, 14 Jul 2011 06:47:52 GMT

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 .

Help needed in writing this expression!!

Thu, 14 Jul 2011 16:58:21 GMT

I have made some changes in this post, thought now it'll be easy for all to understand. Its kinda urgent. Please help.

topic Help needed in writing this expression!! in QlikView

Help needed in writing this expression!!

Help needed in writing this expression!!