Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Help needed in writing this expression!!

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
14/25/2006M9/28/2006
2-M9/28/2006
38/28/2005F9/28/2006
4-F9/28/2006
5-M9/28/2006
14/25/2006M10/28/2006
2-M10/28/2006
38/28/2005F10/28/2006
49/28/2006F10/28/2006
5-M10/28/2006
6-F10/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 .

1 Reply
Not applicable

Help needed in writing this expression!!

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

Community Browser